In this article I will introduce some scripts or stored procedures for SQL Server 2005 that I use regularly as part of the administration of my servers. These scripts let loose to track the use of a server, the evolution of the database size, the use of indexes and their automatic rebuild or verify the date of last use of a table.

Continue reading "5 common administrative tasks of SQL Server 2005"

On one of my SQL servers my event log is filled with errors login SQL Server Agent because of an error per minute:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
User: CORP \ Usr_SQLServerAgent
Computer: SLQ1
Description:
Login failed for user 'DOMAIN \ Usr_SQLServerAgent'. [CLIENT: <local machine>]

In SQL Server, go to SQL Server Agent and right click on Job Activity Monitor, then click View Job Activity. You should see a job failure is one that causes the error login.

Job History

To get rid of errors simply right click on the job in question and click Disable Job.

In most small organizations with limited resources, and the tempdb database systems are often placed on the system disk and in the worst case bases are also the users. To improve performance in I / O on disks, it is better to move the user databases on a RAID 10 (for example) independent and tempdb on a RAID 1 independent.

Move the user databases to another drive does not pose any particular problem, just detach the database, then move them to attach them.

Move tempdb is not a very big problem but it will not provide a restart of SQL server, which can be a little tricky in the case of servers used continuously.

Continue reading "Move tempdb SQLServer"

SQLServer 2005 offers three roles database giving rights on the visualization, the creation or programming work for the SQLServer Agent: SQLAgentReaderRole, and SQLAgentOperatorRole SQLAgentUserRole in ascending order of rights. These rights are visible in detail in the msdb database (Security> Roles).

To give permission to create / edit / programming jobs to a user, for example, a developer, just run the following query:

USE msdb
EXECUTE sp_addrolemember
@rolename = 'SQLAgentUserRole',
@membername = 'username'

Pour supprimer ces droits il suffit d’exécuter la procédure sp_droprolemember avec les mêmes paramètres.

Voici deux guides de migration en Anglais vers SQLServer 2005 et 2008 publiés par Microsoft. Ils sont extrêmement complets (350 et 490 pages) et couvrent des sujets allant de la planification de la migration aux procédures à suivre dans le cas de migration de configurations à haute disponibilité en passant par les changements d’outils de management.