The transaction log for database is full. To find out why space in the log cannot be reused OR Could not allocate space for object because the filegroup is full Reema: Hello DBA it seems our application not working today. We got some error it looks like some problem with the database. DBA: This error mostly occurs because of insufficient space on the drive. Have you checked the drive space? Reema: Yeah, I have checked the drive space in a server –> my computer. It looks I have plenty of space on the server. DBA: Can you check the database auto growth setting in the database? Reema: I have 30…
-
-
Find the latest databse backup SQL server
Use the simplest script to find the latest database backup and the location. -- To get the backup history SELECT top 100 b.server_name ,b.database_name,b.user_name, f.physical_device_name, b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type, b.is_copy_only--,compressed_backup_size/1024/1024 AS C_size_MB FROM MSDB.DBO.BACKUPMEDIAFAMILY F JOIN MSDB.DBO.BACKUPSET B ON (f.media_set_id=b.media_set_id) --WHERE database_name='test' --AND B.type='d' --and b.backup_finish_date >='2018-08-02 22:37:50.000' ORDER BY b.backup_finish_date DESC -- To get the backup type size based on the date select sum(size_mb )from ( SELECT sum(b.backup_size /1024/1024)as size_mb,b.database_name,b.backup_finish_date FROM MSDB.DBO.BACKUPMEDIAFAMILY F JOIN MSDB.DBO.BACKUPSET B ON (f.media_set_id=b.media_set_id) WHERE b.backup_finish_date between '2012-04-25'and '2012-04-26' AND B.type='D' group by b.database_name,b.backup_finish_date --ORDER BY b.backup_finish_date DESC )as ab You can add more conditions on where clause http://msdn.microsoft.com/en-us/library/ms186299.aspx. Like backup Type…
-
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…
-
How to get the SQL server critical errors via mail using native T-SQL Script
T-SQL Tuesday #015: Automation: I am very much interested to participate in the T-SQL Tuesday event party. I have seen the T-SQL Tuesday logo and article in one of my favorite SQL server blog Gail Shaw. I found the Adam Machanic ( Blog | Twitter ) via Google but, I did not find the required information so, I wrote an email to him and he has sent me the link to topic http://sqlasylum.wordpress.com/2011/02/01/invitation-to-t-sql-tuesday-15-automation-in-sql-server. Thanks to Adam Machanicfor doing this wonderful job. This is my first T-SQL Tuesday post. I have referred some of T-SQL Tuesday blog post. This month Pat Wright is hosting and has given us the…