Category Archives: Tricks

Add a table to an Existing Transactional Replication using T-SQL and Generate Snapshot using T-SQL

Replace the PublishedDB with your published database and TableName with the table name you want to replicate The next step is to start the snapshot agent to pick up the new tables added to replication and replicate across to Subscriber … Continue reading

Posted in Replication, SQL Server, T-SQL Scripts, Transactional Replication, Tricks | Leave a comment

Split a string using a table Variable

Was just playing around with Table variables and thought of a practical problem lot of people approached to me with. Thought its worth documenting on how I do it so somebody can reuse the trick. Given a set of names … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , , | 1 Comment

Two main tables in a distribution database to facilitate replication

Somebody asked me this question: “What are the two main tables in a distribution database when you configure replication“ The two main tables in a distribution database to facilitate replication are those that contain the information on where to start … Continue reading

Posted in Replication, SQL Server, Tricks | Tagged | Leave a comment

Script to find when a table was last modified

I have had a question from one of my collegues, how to find when was a table last modified. Here is what I suggest to use to find when was tables schema changed and when was the last user update/delete … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , | Leave a comment

SQL Server configurations List

Was just checking sql server configuration of my sql installation via t-sql and found a way of listing which sql server configurations can be changed dynamically. Here is the list of SQL Server configurations with their descriptions, that can be … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged | Leave a comment

Read SQL Server error Log and SQL agent log

Often DBA’s need to quickly look at sql server error log and agent logs to find something speicific, and it takes awful lot of time to go through a huge error log file; and you want to get the information … Continue reading

Posted in SQL Server, sql server error log, T-SQL Scripts, Tricks | Tagged , , , | Leave a comment

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’ This error message is raised when your user account does not have the db_owner role of a database meaning you are not the owner of the … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks, Troubleshooting | Tagged , , | Leave a comment

Check SQL Wait Types to identify potential problems on sql servers

Production DBAs often end firefighting issues on live servers and this can easily side track the DBA’s from the actual problems underlying a Database or at sql server instance level. This is where my Sproc will come in handy.  I have … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks, WaitTypes | Tagged , | 2 Comments

How to run multiple SQL scripts

Scenario 1:- Create a .bat file with the below code and store the .bat file in the folder containing all the .sql scripts. setlocal SET SQLCMD=sqlcmd -S<servername\instancename> -d<database name> -E for %%d in (*.sql) do %SQLCMD% -i%%d endlocal Double click … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , | Leave a comment

Execute SQL script from command prompt

We are often required to run certain scripts or deploy scripts to sql server using command prompt; here how we can do it seemlessly. Open command prompt and type in the below sqlcmd mentioning the relevant servername /database name and … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , | Leave a comment