Reset mysql root password

To reset the mysql root password follow these steps 1. Stop running mysql instances # service mysqld stop 1a. If that doesn’t work then kill all running mysql instances # pkill mysql 1. Stop running mysql instances # service mysqld stop 2. Start Mysql in safe mode # mysqld_safe –skip-grant-tables 3. Run these command replacing . . . → Read More: Reset mysql root password

SpiraTeam query all users and roles

I needed a script for an audit of SpiraTeam to query all users and roles. And here it is:

/****** Script for SelectTopRows command from SpiraTeam ******/ SELECT TOP 1000000

[FULL_NAME] ,[PROJECT_ROLE_NAME] ,[PROJECT_NAME] ,[ACTIVE_YN] FROM [SpiraTeam].[dbo].[VW_PROJECT_USER] where ACTIVE_YN like ‘Y’ ORDER BY FULL_NAME ASC

mssql truncate all log files

Learn in MSSQL how to truncate all log files! This transact-sql statement uses the core truncation method recommended by Microsoft.mssql truncate all log files

mssql truncate all log files on a MSSQL 2000, 2005 server DECLARE @db varchar(255) declare @sql varchar(200) DECLARE curName CURSOR LOCAL FAST_FORWARD FOR select name from master.dbo.sysdatabases where dbid > . . . → Read More: mssql truncate all log files

Connecting To A Database Using Windows Authentication With Different Credentials Using SQL Server Management Studio

This info courtesy of inotekgroup and Paulo Morgado:

The problem

You want to use a different username and password to connect to a SQL Server but the Windows Authentcation Username/Password are greyed out / disabled!

When connecting to a SQL Server using Windows Authentication, there is no way to type in an alternate domain/username through . . . → Read More: Connecting To A Database Using Windows Authentication With Different Credentials Using SQL Server Management Studio

SQL Server: How to add an identity column to a table with data.

This post thanks to the caveman blog! All hail the caveman blog!

http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/

This article demonstrates a step-by step procedure about how an identity column an be added to a table that has data. There are two ways of adding an identity column to a table with existing data:

1. Create a new table . . . → Read More: SQL Server: How to add an identity column to a table with data.

Server user ‘td’ is not a valid user in database

Error:

Messages: Failed to Get Projects Properties; Failed to get projects properties; Failed to save projects properties; Cannot build directory item for key ‘…… …….. [Mercury][SQLServer JDBC Driver][SQLServer]Server user ‘td’ is not a valid user in database

If this is a SQL 2000 database detach and reattach with enterprise manager and make the owner ‘sa’.

. . . → Read More: Server user ‘td’ is not a valid user in database

in TD Tables Struct Dir; [Mercury][SQLServer JDBC Driver][SQLServer]Invalid object name ‘SEQUENCES’.;

How to fix

Failed to determine structure in TD Tables Struct Dir; [Mercury][SQLServer JDBC Driver][SQLServer]Invalid object name ‘SEQUENCES’.

This error may occur with Mercury Quality Center 9.0 if you move a project between databases.

The fix:

1. Detach the database 2. Reattach the database and make the owner ‘sa’

SELECT permission denied on object ‘USERS’, database ‘qcsiteadmin_db’, owner ‘dbo’

How to resolve this error:

[Mercury][SQLServer JDBC Driver][SQLServer]SELECT permission denied on object ‘USERS’, database ‘qcsiteadmin_db’, owner ‘dbo’.; Stack Trace: java.sql.SQLException: [Mercury][SQLServer JDBC Driver][SQLServer]SELECT permission denied on object ‘USERS’, database ‘qcsiteadmin_db’, owner ‘dbo’. at com.mercury.jdbc.base.BaseExceptions.createException(Unknown Source) at com.mercury.jdbc.base.BaseExceptions.getException(Unknown Source)

The reason this error is occurring is because user td does not have rights to the tables . . . → Read More: SELECT permission denied on object ‘USERS’, database ‘qcsiteadmin_db’, owner ‘dbo’

Howto: check database fragmentation sql server

Howto: check database fragmentation sql server

How to find all indexes that have fragmentation over 25%:

select * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, ‘LIMITED’) WHERE avg_fragmentation_in_percent > 25.0 AND index_id > 0 AND page_count > 1000 order by avg_fragmentation_in_percent desc

truncate sql log

First off, You CANNOT REVERT this command, so be sure you want to truncate the log! THE QUERY Instructions: Just replace with your DB

BACKUP LOG WITH TRUNCATE_ONLY DBCC SHRINKDATABASE( ”, TRUNCATEONLY)