Microsoft SQL Server 2008 R2 SP1 CTP SQL Server 2008 R2 SP1 10.50.2418.0 CTP is available now. You may download and test it. http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409
-
-
Meme Monday: I Got 99 SQL Problems And the Disk Ain’t One
Meme Monday: I Got 99 SQL Problems and the Disk Ain’t One I am a bit late to this party. I have seen this post from Grant’s blog now only. Thanks to Thomas LaRock such a great topic. Here is mine: 1. Too many indexes on a table Developers didn’t check their codes and an execution plan too. They just go and create the indexes (Duplicate indexes too). 2. Improper memory setting Improper min, max memory setting especially for 64-bit servers. 3. Improperly configured transaction log Whenever I got a new server, I have seen log backups are failing because, there is no current database backup. BACKUP LOG cannot be…
-
Shrinking the log file script for SQL server database
Shrinking the log file script for SQL server database This script mainly for a DBA’s, because most of the time the log file grow again and again and eats up all the disk space and finally an application goes down. Errors from SQL server error log: Error: 9002, Severity: 17, State: 2. The transaction log for database ‘test’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases If you got an error “Error: 9002, Severity: 17, State: 2.” like this go and check the log_reuse_wait_desc column in sys.databases by running below command and see what value it returns. SELECT name,…
-
INDEX DEFRAGMENTATION SCRIPT for SQL server 2000
INDEX DEFRAGMENTATION SCRIPT SQL 2000 Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure. Use the DBCC SHOWCONTIG statement. It will display the fragmentation information. Here I used 3 conditions: Fragmentation >=30 AND PAGES>1000 then rebuild Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics If the above conditions are false then update the statistics Before you going to run the procedure…
-
Difference between truncating and shrinking the transaction log file
Difference between truncating and shrinking the transaction log file I have noticed in many forum the frequently asked question is “Truncating and shrinking the log file both are same” The answer is obviously NO. These both are totally different. Before going into discussion, let’s read the transaction log file architecture. Truncating the transaction log: Truncating a log file will change one or more VLF file’s status from active to inactive and mark the space for reuse. SQL server will change the VLF from active to inactive and reuses it automatically (Just like a circular). When does it truncate and reuse it automatically? It depends upon your setting: recovery…