Tag: SQL Server

Posted on

MS SQL Database mirrored database stuck in “recovery pending” state

Today, I had to troubleshoot a situation where, following a myriad of improbable events:

  • A MS SQL mirrored database was in “recovery pending” state on one server.
  • We had to bring it back online on the server.

I initially tried to force the database back online by issuing:

ALTER DATABASE <db_name> SET PARTNER OFF;

However, I’ve got as a reply the message”Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details”. Of course, permissions were fine and there was plenty of free space.

The solution was to:

  1. Move the database and log files (.mdf, .ldf) to another location.
  2. Drop the database.
  3. Move the database and backup files back to their initial location.
  4. Re-attach the database.
  5. Reconfigure SQL Database Mirroring.

Back online!

Posted on

Old memories of being a SQL Server DBA

There are some days like that, I feel a bit nostalgic : I’m not anymore a full-time DBA since two years.

Today, what made ​​me think of that distant time was a simple question: how to allow a user to execute all stored procedures in a specific database?  The answer is simple : create a new role and assign it to the database user. The beauty is that it’s even easier to do than to say!

USE myDatabase;
GO

-- create a db role to execute stored procedures
CREATE ROLE db_spexecute;

-- grant execute to role
GRANT EXECUTE TO db_spexecute;

-- assign role to user
EXEC sp_addrolemember N'db_spexecute', N'myUser';
GO

Posted on

Bonnes pratiques SQL

Voici une liste de quelques best practices pour la création de tables, d’instructions et de procédures stockées rédigée à partir de quelques articles glanés au fil du temps. Il s’agit davantage d’un aide mémoire que d’une liste d’éléments rigides à respecter ; il faut mettre en perspective plusieurs facteurs (fréquence d’utilisation de la requête, quantité de lignes de données à la source, jeu d’enregistrement retourné) lorsque l’on rédige des instructions SQL afin d’évaluer l’importance de respecter ces pratiques. Continue reading “Bonnes pratiques SQL”