Over in the SQLTeam forums, eyechart has posted information about running SQL Profiler without sysadmin rights. I recently granted sysadmin rights to a developer so that he could run SQL Profiler in the development environment.
Read more →
Reporting Services Tip #3 - rs.exe, backslash problem
In a previous blog, I wrote how you can deploy your reports via Report Manager. It was mentioned in the comments section that "you can use the web service to upload the RDL directory from code".
Read more →
EDIT: fixed problems found by gam.
I have made yet another change to the DBCC DBREINDEX stored procedure. I modified it so that when a clustered index gets rebuilt, we don't rebuild the other indexes since that happens automatically for us.
Read more →
Over in the SQLTeam forums , we had a long discussion/debate about paging in SQL Server 2005 . Adam Machanic shows us his solution to this problem.
Legacy Comments
Shaju
2004-11-29
re: Paging in SQL Server 2005 Hi Tara,
Can you send me the rss links for
SQL Server - Database Administration (rss) SQL Server - General (rss) SQL Server - Reporting Services (rss) Thanks
Shaju
davidmartin
2005-07-04
re: Paging in SQL Server 2005 help me out
4r paging sql server database with jsp
10 records per page
Munawar Ali
2006-11-24
re: Paging in SQL Server 2005 http://davidhayden.
Read more →
Benjamin Jones shows us an example of using extended triggers to capture DDL statements. Very cool!
For those of you wondering...DDL - data definition language - examples: CREATE/ALTER/DROP TABLE, CREATE/DROP INDEX
Read more →
EDIT: Please see my new version of the isp_Backup stored procedure.
Here's the new version for the backup transaction log stored procedure:
---------------------------------------------------------------------------------------------------- -- OBJECT NAME : isp_Backup_TLog -- -- AUTHOR : Tara Duggan -- DATE : May 12, 2004 -- -- INPUTS : @Path - location of the backups -- @Retention - number of days to retain transaction log backups -- OUTPUTS : None -- DEPENDENCIES : None -- -- DESCRIPTION : This stored procedure performs a transaction log backup on the non-log -- shipped user databases that do not have SIMPLE set as the recovery model.
Read more →
EDIT: Please see my new version of the isp_Backup stored procedure.
I decided to finish posting the new versions of my database maintenance routines. Here's the new version for backing up your databases using SQL LiteSpeed:
Read more →
A common mistake when first creating a trigger is thinking that the inserted or deleted tables will contain only one row. This is not true. However many rows were affected by the INSERT, UPDATE, or DELETE is how many rows will be in the trigger table.
Read more →
Reporting Services Tip #2:
The easiest way to install Reporting Services is to have the Report Server and SQL Server on the same server. It's the easiest way, but it certainly is not the best.
Read more →
EDIT: Please see my new version of this stored procedure.
I've been asked a few times if there was a way to change the number of days to retain the backup files for in my backup stored procedure.
Read more →
Due to the problem mr_mist found in the DBCC DBREINDEX stored procedure, I have an updated version of the DBCC INDEXDEFRAG sproc as well:
------------------------------------------------------------------------------------------------------ OBJECT NAME : isp_DBCC_INDEXDEFRAG---- AUTHOR : Tara Duggan-- DATE : May 11, 2004---- INPUTS : @dbName - name of the database-- OUTPUTS : None-- DEPENDENCIES : None---- DESCRIPTION : This stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the database.
Read more →
EDIT: This stored procedure has been updated.
Earlier this month, I posted a stored procedure that runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc.
Read more →
I would like to make some changes to code that I posted for database maintenance routines. I'm wondering what your opinion is for modifying the originals or creating new ones. I need to change isp_DBCC_INDEXDEFRAG and isp_DBCC_DBREINDEX as the way it is coded now it will only do it for one of the indexes on each table.
Read more →
If you've got an MSDN subscription, head on over to MSDN to download SQL Server 2005 Beta 2. The iso image file is 595.5 MB. I downloaded it this morning in under 30 minutes.
Read more →
In our test environment, we have a SQL Server that has four named instances on it. For one of the instances, I was configuring it to be the distributor for replication.
Read more →
EDIT: Updated the link to the new version of isp_Backup.
Over the past couple of weeks, I have posted various database maintenance stored procedures. Here's a listing of them:
isp_Backup - full, differential, and transaction log backups isp_DBCC_CHECKDB - perform integrity checks on all databases isp_ALTER_INDEX - defragments and rebuilds indexes isp_UPDATE_STATISTICS - performs update statistics on each table in the database isp_Restore - restore newest BAK file in specified directory Do not put these in the master database.
Read more →
Kristen recently asked why his msdb database had grown so large (750MB). He found out that it was due to the sizes of the backup system tables. These tables store information used for backup and restore operations.
Read more →
EDIT: This stored procedure has been updated.
The below stored procedure runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc. I recommend putting it into an Admin database (hey just name it Admin!
Read more →
You've probably already heard by now about SQL Server 2005 Express Edition. It's the next evolution of MSDE 2000. Here's the Top 10 Cool Things about SQL Server 2005 Express Edition:
Read more →
EDIT: This stored procedure has been updated.
The below stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the user database that is inputted into the sproc. I recommend putting it into an Admin database (hey just name it Admin!
Read more →