Almost every application DBA would have a need to document the various database objects such as tables, views, stored procedures and functions. I wasn’t an exception and exploring ways to document the 400+ tables we have. Basically, this would serve the following purpose:
I was thinking about writing a script to capture the objects that were either CREATED/DELETED or MODIFIED in the last 24hrs. Instead of directly querying the sys.objects I from where I can only get minimal information I decided to get a whole bunch of useful information using the SQL Server default trace.
By default the default trace will be running in all the SQL Servers since SQL 2005 onwards. It will capture minimal information without overloading the database. It will be placed in the same location as that of the SQL Server errorlog. The maximum file size is 20MB after which the trace will roll over into a new file. However, SQL Server can maintain only the 5 most recently created trace files. In addition a new trace file is generated whenever SQL Server is restarted.
I was doing an upgrade of one of our development servers to SQL Server 2008. To minimize the downtime I decided to attach all the user databases using script. Basically, I am attaching the db from the same location there were present earlier to being detached.
Consider the following example, If I have a database named “Test” in SQL 2005 that is currently residing in the following locations, C:databaseTest.mdf & C:databaseTest_log.ldf and another database named “Test_2008″ in C:temptest_2k8.mdf & C:temptest2k8_log.ldf then the script I wrote would prepare the script to attach the database test from the same location.
I had to prepare the list of tables having more than 60% fragmentation. I wrote this query to get those information. The below query will give the list of all the fragmented tables in a particular database having more than 60% fragmentation. This will work for SQL 2005 and beyond.
There are cases where we need to pass the input parameter to the stored procedure as a comma-separated string in order to avoid multiple db calls from happening. This helps in reducing the db calls thereby reducing the load on the db server.
The below function will split the given comma-separated string into integers and process the results.
Amit Bhatt
| #
Hi Deepak,
Thanks for such a nice article.
You missed one thing to add in code:
@article = ‘all’,
Hence the script will be like this:
EXEC sp_addsubscription
@publication = ‘mypublication’,
@article = ‘ALL’,
@subscriber = ‘Subscriberservername’,
@destination_db = ‘mydestinationdbname’,
@reserved=’Internal’
Error 18486 | Platformblog
| #
[...] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. [...]
Setting and Changing Collation – SQL Server 2008 « Blog
| #
[...] to sql-articles.comRead more: http://sql-articles.com/articles/dba/how-to-change-server-collation-in-sql-server-2008/#ixzz1pu2S8XW… Like this:LikeBe the first to like this [...]
VidhyaSagar
| #
Naveen,
I’ll check this out and get back to you.
balakiran
| #
Thanks man, Very simple & easy to understand !!!!