Questions tagged [tsql]

Transact-SQL is the Microsoft extension of ANSI standard SQL.

58 questions
16
votes
1 answer

How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL?

I'm managing about 90 SQL Server instances and need a query to determine how much memory has been allocated to each instance. Please note, I'm not asking how to set it, just to see what it's currently set to without using the Management…
token
  • 275
  • 1
  • 3
  • 9
7
votes
2 answers

dbcc checkdb('XYZ', REPAIR_ALLOW_DATA_LOSS) indicating that XYZ is already open and can only have one user at a time

I have a DB, XYZ, that had a corrupt log file and now since the log file is totally unusable this is rendering this specific DB unusable and I need to be able to rebuild it. I've already done a lot of research into this process but am getting stuck…
Christopher Bruce
  • 329
  • 2
  • 4
  • 14
6
votes
1 answer

Copy SQL Server Databases

I am looking to "move" databases to different servers with minimal disruption to data and service. These databases vary in size from 5GB to 140 GB. I've seen and even used some of the various data transfer tools of SQL Server but I'm unsure of what…
4
votes
1 answer

MSSQL: Restore a DB to the latest file in a backup set

I have a MSSQL 2008R2 DB in SIMPLE recovery mode that is periodically backed up with BACKUP DATABASE MyDB to DISK = 'Z:\MyDB.cbak' WITH COMPRESSION This runs periodically, creating multiple backups sets in the file. I want to restore the latest…
DrStalker
  • 6,676
  • 24
  • 76
  • 106
4
votes
1 answer

Restore database from backup file, where file name matches reg expression

I am doing the below : RESTORE DATABASE UAT FROM DISK = 'E:\Databases\backup\MY_LIVE_20120720_070001.bak' WITH REPLACE But I want to be able to use a file location that ignores the numbers in the file name (which represent the date) in my back up…
NimChimpsky
  • 460
  • 2
  • 5
  • 17
3
votes
1 answer

How to rebuild MS Agent Jobs from MSDB table?

This morning our DB server died. They restored backups to another server but these didn't include the many MS Agent jobs. Anyway I have managed to get a backup of the old MSDB table where all the tables used to create MS Agent Jobs are held added to…
MonkeyMagix
  • 131
  • 1
  • 3
3
votes
3 answers

if I select more than 15 fields, I get catastrophic failure

Using sql server 2012 I am connecting to a linked server with Pervasive SQL on it. When I do select * or select field1,field2,field3....field15 I am getting this error: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked…
Alex Gordon
  • 455
  • 3
  • 14
  • 31
3
votes
2 answers

SQL 2008 - Transaction log is full

We have some issues with the transaction log files We have SQL Server 2008 Our situation: Every night, we have many jobs that runs on the server by SQL Agent Some jobs stop because the transaction log for database is full Everything is fine when we…
Cloudlucky
  • 31
  • 4
3
votes
1 answer

Overflow when converting int to datetime

I'm currently importing a table from MYSQL to MSSQL. There's a column there for storing date, only it's stored as an number. When i import it to MSSQL i get it in as an int data type. When i try to convert that to datetime I get an : Arithmetic…
Paul
  • 714
  • 2
  • 6
  • 19
2
votes
1 answer

Full-text catalog timeout, SQL Server 2005

we are re-loading a db with new data using backup/restore process. Once restore is complete, we re-build the full-text catalog. Full text searches work, no timeouts for about a day or so. However if the search hasn't been used for a day, two days,…
2
votes
4 answers

SQL Server I/O Error 21

I am trying to take backup of one DB Which is showing Continuosly I/O Error 21 (The device is not ready) deteceted during read at offset 0x000000000000000040 in file e:\test.ndf Error 823. The Step Failed. The backup was running smoothly for last 2…
unknown (google)
2
votes
1 answer

SqlCmd : Login timeout expired from localhost

I've setup the instance SQLEXPRESS via SQL Server 2008 R2 installation, added a security login with all server roles, one called 'sqluser'. The server authentication is SQL Server and Windows Authentication mode. However, when i specify the -S…
mschr
  • 48
  • 1
  • 2
  • 6
2
votes
1 answer

How can I create a database backup replacing the previously created backup file?

If I backup my SQL Server 2008 R2 database with the following command set @BackupFileName = N'C:\backup\Production-db.BAK' BACKUP DATABASE [Production-db] TO DISK = @BackupFileName and run the command twice, the backup file grows to twice the…
Adrian Grigore
  • 1,074
  • 3
  • 21
  • 33
2
votes
6 answers

Restore Database User to Server Login on SQL Server 2008

We have a database backup we need to restore on a server that doesn't have the logins for the database... all the users are in the database though so we are hoping we can restore it this way? I've played with sp_change_users_login but it only works…
sontek
  • 123
  • 1
  • 1
  • 5
1
vote
1 answer

How to take the column name dynamically with dates

I have column name with dates which comes from a table and changes from current month to last 41 month. and i have to write the column name again and again to reach the desired output. Is there any way I can take the column name dynamically. Select…
user506566
  • 11
  • 2
1
2 3 4