Need a backup script because xp_sqlMaint is not running on 2008? Try this one. You need only fill in the database name and directory name in the appropriate places below:
Category: SQL Administration
Out of disk space…Log file grown too big?
It’s early in the morning, and you’ve got disk space problems… log file, right? Yep. OK, we need a script that will tell us what we need to know ASAP. Since i experienced this issue multiple times…and since they always…
Find the users and their roles in a Database
Do you need to know all the users for a single database…and also to know what roles they have? (The key is to look for the string “mscs” in the code and replace it with what your databases beginning strings…
Improved EventLogs in Win 2008
Recently at one client location we had numerous unexplained cluster failovers. I had SQL Server and Analysis Services running on the clusters. When analysis was done on the failover, the analysts found that the “FlightRecorder” showed errors immediately before the…
Determine “Locked Paged” use across your domain…
So, building on my previous blog entry, now, let’s open a multi-server query and run this script that follows. It will tell you for each server it’s version and how much memory is in locked pages.
Programmatically act on SQL Version
I often have scripts that run across multiple versions of SQL … more and more 2008’s and less and less 2000’s. In doing so, i need to take different actions based on that version. Here’s the simple technique that i…
MSDB db too big?
Microsoft provides “sp_delete_backuphistory ‘6/1/2011’“. However, this can take a very long time unless you also add indexes to the backup table. This is what did the trick for me: Create Nonclustered index idx_backupset_finishdate ON backupset (backup_finish_date) INCLUDE (backup_set_id, media_set_id) What…
Impersonation, Execute As & Remote queries
This week i had a developer creating a process (job) where a database user was to have authority to query remote machines. To debug the process the developer needed to assume the role of the user running in the application. In…
SQL Server’s Auto Growth settings
Over the last week I’ve been finding an unusual number of log file growths. I did some investigation and found a good script to help me track what was the current status for file growths. It is as follows:
Count your lines of code
How much code has gotten into production? How many different objects have this code? Isn’t that also a measure of just how ‘big’ your responsibility is? If you have a massive application with thousands of stored procs and functions…guess who…