Alek Kirstein Sunday, May 10, 2009 at 10:53PM Use this batch to quickly shrink all database log files, freeing up any space at the end of the file. To shrink even more, first complete a full database backup, then run this:
Alek Kirstein Sunday, April 26, 2009 at 11:37PM This installment is part of a “Security Management” series of articles for SQL Server.
We have covered getting all security principles and their role memberships, and the role/group memberships of those, to the top of the membership tree. This procedure collects all the security granted to principles/roles/groups for all databases on the SQL Server instance.
Alek Kirstein Sunday, April 26, 2009 at 5:24PM This proc returns the total row counts for each table in a database.
I use this to track and analyze table growth rates by record count across all databases by using this as the input query to an insert statement that adds all the row counts and the current date/time. Then trend lines can be plotted to gauge growth rates and enable capacity forecasting.
Alek Kirstein Thursday, April 16, 2009 at 1:35AM —Contributed by William Cassidy, Collectors Universe, Inc. (a unique and awesome developer) – Thanks William!
—Run these queries to generate a series of SQL statements.
—Tweak the source table and WHERE clause if needed.
—Afterward, copy the generated SQL statements into Management Studio and run them. They take about 25 seconds.
—Then, you’ll have a table #temp that contains the values (cast as varchars) of all columns in the table.
Alek Kirstein Sunday, April 12, 2009 at 1:32PM This is a part in a series of “HOMEGROWN COMPLIANCE” articles dealing with various aspects of meeting compliance related goals in the current general corporate compliance environment (ISO, SOX, ISA, CPI, etc.)
This installment provides the stored procedure and description how to collect the data from the default trace that runs, you guessed it, by default in SQL Server 2005. Also included is a view to join to event descriptions for key fields. What is nice is that additional traces, hindering performance, are not needed for this approach as the default trace is usually already consuming resources. This method provides a way to utilize that trace data.
Alek Kirstein Sunday, March 29, 2009 at 5:33PM This installment is part of the “Security Management” series of articles for SQL Server.
Find all group memberships, and their group memberships, all the way to the highest group/role/principle membership. This will be correlated and joined to another procedure (in an article to be published soon) together returning all granted and inherited protects on the SQL Server instance for every principle and every role/group.
Used in several areas e.g. compliance, general security auditing, baseline comparisons, security inquiries, deployment cleanups, etc.
Find all principles with memberships to groups/roles and memberships of those groups/roles all the way to the top principle:
Just a few example uses:
Alek Kirstein Saturday, March 21, 2009 at 7:12PM This is a resource light query that can be run repetitively to identify blocking or used at the time of blocking to collect/report the pertinent information quickly, and then possibly email or log that for review. It can be very useful to collect and analyze blocking data through key trend charts, i.e. application/traffic growth compared to blocking trend, maintenance effects on application blocking, new deployment monitoring and cross server blocking.
Alek Kirstein Monday, March 16, 2009 at 9:15PM …a little gotcha for setting settings in SSMS, if you turn on the option to script an IF NOT EXISTS clause then this is what the modify stored procedure script comes out as below (just the first part pasted). Ouch! This will never modify anything and still return: Command(s) completed successfully.
Alek Kirstein Sunday, March 15, 2009 at 6:31PM Uses xp_cmdshell (note: it is not a recommended practice to enable these procs) but is a useful tool to bcp all tables in a database (@dbname) into a directory (@filepath i.e. ‘c:\’ ).
Alek Kirstein Monday, February 23, 2009 at 12:18AM Alek Kirstein Sunday, February 22, 2009 at 1:36PM Very useful in identifying all kinds of valuable database statistics and information, e.g. non-standard settings, large files, incorrect recovery modes, database files located on system drive, etc.
Alek Kirstein Sunday, February 22, 2009 at 1:57AM Problem:
Say goodbye to the frustrations over getting quick, exclusive access to a database during the restore or repair of that database while end users keep gaining access without cease. This sp will get the database into single-user mode fast after diligently 86ing all spids. The issue is that you can kill and kill spids but other users can still get into the db until the ‘set single user mode’ is executed. A looping kill statement with a poised ‘set single_user’ statement works nice. For heavy traffic environments, increase the max loop limit and shorten the wait time value.
Basic logic:
1. kill all users in the db
2. verify all are killed
3. re-kill if needed (up to 10 retries a few seconds apart)
4. immediately set the database in single user mode for the current query window (where restores/repairs can be done)
5. provide the statement to set database back to multi-user mode if needed.
(note - this will only execute specifically for users in the ACL list (see the code below) and will not work on system databases)
Alek Kirstein Sunday, February 8, 2009 at 10:22PM This is an installment in the “SQL Agent Toolbox” series of articles for SQL Server.
Question: How do I know if a job for some reason did not fire as was called for by the schedule?
Reporting failed jobs leaves out an important notification that is rarely caught by most task management systems: Missed jobs! This proc will return jobs that didn’t make it to run as was expected by the scheduling system behind SQL Server Agent.
Alek Kirstein Sunday, February 1, 2009 at 8:21PM This is an installment in the “SQL Agent Toolbox” series of articles for SQL Server.
This proc returns all the failed job steps from SQL Server Agent history tables. This is useful to see how many times a job has failed regardless of notifications and the current run status of the job.
This work is licensed under a
Creative Commons License.
