If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.
View 5 RepliesRecently I created a DDL Server-scope trigger using the following:
create trigger stop_alter_database on all server
for ALTER_DATABASE
as
rollback;
print 'database change stopped by stop_alter_database';
go
Then I ran the following script:
alter database [test] modify file (name=test', maxsize=2028mb);
The result was:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
database change stopped by stop_alter_database
The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?
System Configuration :
OS : Windows 2003 latest SP
SQL Server : Standard Edition, SP2
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
DownTime : This is not a 24x7 kind of machine. It can have downtime
Reference : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1198044&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1
I have been discussing this Service Broker issues in this forum for quite sometimes and sorry to bother u all again€¦ To make things more clearer before implementing in production environment I have few doubts and it should be clarified..
As discussed in the first link we can clear sys.conversation_endpoints by just giving ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE. But my apprehension is that if we run this command on production server and it truncate this table€¦ what will be the impact and overall overhead on the system€¦ Is it recommended to give this statement on Production Server daily at less traffic times, to clear this table ?€¦ will it have adverse effect. I repeat I have downtime , I can even shutdown this server daily€¦
I also, just want to know why Microsoft has not looked into this aspects€¦ why the system itself is clearing the expired messages.. What is the thought behind this architecture
I have the script to run in batch€¦ but in high-level meetings it is always difficult to convince this architecture/process€¦
Thanks in advance
Primary platofrm: Sql12k, 7.0 Ultimate Pro OS
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Testing team is performing some activities on MSSQL - 2008 R2 database ..and after completion of the test, again team wants to restore the original database ( in the same state as before starting of the testing). Currently team is taking the back-up before starting testing and restoring the backup after the testing .. Since the database size is huge, this restoration is taking more time Is there any better way of taking the database to the previous state in lesser time.
View 3 Replies View RelatedHi,
We have an order posting stored procedure that's been executing for 14 hours now. This procedure has simple update statements but all the tables updated have triggers which update other tables with triggers and so on.
This procedure hasn't commited yet. I have a full database backup from last night and transaction log backup from this morning while it was running. I want to kill this job and restore database from last backup. Only thing I am afraid of is that it will take too long to roll back which I have no estimate of time. Is there any faster way to get rid of all the uncommited transactions? Has anybody rolled back such a massive transaction? Any idea on how long it can take? Can it affect the overall server performance as this is the main production ERP server.
I can see in profiler that this procedure is still running and not hung.
I have a wrapper stored procedure ProcMain_wrapper that executes in one main dataabase and it calls child stored procedure in multiple databases. Does the rollback occur in all the databases if the stored procedure fails in any one of the multiple databases?
create procedure dbo.db_Main.ProcMain_wrapper (
declare @curClientCode as cursor ,@db varchar(10),@dbName varchar(20)
BEGIN TRANSACTION TRAN1
BEGIN TRY
SET @curClientCode = CURSOR FOR
SELECT [name] from sys.databases where name like 'dbclient_%'
[code]....
Hi all,
I have a table with 4869068 rows and when some one tries to insert the records into this table the database times out....Does any one know what could be the reason and from where do I start debugging.
I have no problem with the disk space?
Thanks,
Venu
Hi all,
Currently we take full database backups nightly for our SQL Server 2000 data warehouse systems. The backups take a very long time over 20 hours and we would like to find a good way to reduce these backup times. How can I change our backup plan to reduce the long backup run times. Data size is 1 TB for our data warehouse database server.
Thanks
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 Replies View RelatedFor security reasons customer wants to put a SQL database on an encrypted thumb drive (IronKey). Here's the rub though. He wants to be able to work with the data on a workstation. Then, if he takes his laptop out of the office he wants to be able to simply plug that thumb drive into the laptop and fire up SQL on the laptop and use that same database. Procedurally this would work in that the database can be created so that the location is the same from both machine viewpoints, however will the two different SQL instances allow moving the database back and forth like this?
View 9 Replies View RelatedI have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
Hi,
View 6 Replies View RelatedHi,
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
Thanks,
I am using SQL 6.5. Can I create a database and Alter the same
database in one SQL Script?
Thanks in advance.
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedI am trying to move one of my database log to different location but I am geeting an error when I try to do this.
I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'
Here the syntax I am using to do this:
alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')
But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.
Thank You,
John
Hi all this is my first of most likely many posts.
I am writting a stored proc that will be used on many diffrent named databases and i am setting the database name with a variable. I would like to use this variable in the following situation any clues on this would be greatly appreciated.
DECLARE @DBName varchar(50)
SET @DBName='Database'
alter database @DBName set recovery full
regards
Phil
Hey,
I am trying to give a user permission to change the name of a database.
They are a member of the server role 'secuirtyadmin'
They are a member of the database role 'db_owner'
They have been granted ALTER permission on the database.
But every time I try and run the command
ALTER DATABASE <dbname> MODIFY NAME = <newdbname>
I get an error saying the database doesn't exist or you don't have permissions.
What am I missing?
Hello,
I have Test database with Log file property Automatically grow the file, option Unrestricted file growth, I wanted to alter it with Restrict file growth upto 200 MB. I'm getting an error that you cannot modify as file doesnot exist
But the file does exist. I cannot figure out what is giving the problem.
below is the sql statements
select name from sysfiles
go
Test_Data
Test_Log
USE master
go
ALTER DATABASE Test MODIFY FILE
( NAME='Test_Log',
MAXSIZE=200MB )
go
ERROR
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'Test_Log' does not exist.
Any help is appreciated.
Thanks
Sejal
Hi
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
des
I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.
I know I need to do:
ALTER DATABASE dbname SET ENABLE_BROKER
But I must avoid including the name of the database in the script. I did wonder if this would work:
DECLARE @DB varchar(50)
SELECT @DB = DB_NAME()
ALTER DATABASE @DB SET ENABLE_BROKER
But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)
The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.
Any thoughts?
Ruth
I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove?
Here's my senario:
We are using SQL 2005€™s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db€™s.
My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well? In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates?
How can I do an alter table in some table that replicated database ?
I got the error message when I try !
Conrad writes "I'm currently working on Table Partitioning. I have done everything succesfull for partitioning, what I'm struggling with is to use
" SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " to get the last used FileGroup. Now this works just fine, but when I run the following script:
--Decalre variables
DECLARE @LastFilegroupName VARCHAR(50)
DECLARE @FilegroupName VARCHAR(50)
--Retuns the next FileGroup to be used
SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY')
SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','')
SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10))
--Alter database statement
ALTER DATABASE VadivelTesting
ADD FILEGROUP @NewFG_Name
This script gives the following error "Incorrect syntax near '@NewFG_Name'."
When I give it a static name it works fine, but not with the variable.
Please can someone help me, I'm in struggeling with this one."
We're upgrading to SQL Server 2005, and we've restored databases from SQL Server 2000 to the development server (SQL Server 2005). Part of what we need to do is alter the database collation to SQL_Latin1_General_CP1_CI_AS; our SQL Server 2000 server is case-sensitive.
My initial attempts to alter the database;
alter database RGDWDEV collate SQL_Latin1_General_CP1_CI_AS
failed because of the presence of computed columns and user functions. So, I wrote scripts to save the definitions of the computed columns and functions, and wrote scripts to drop them. Now that they are gone, I expected to be able to successfully execute the above 'alter database' statement, but I am now getting this error;
Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'sys.sysschobjs' and index name 'nc1'. The duplicate key value is (0, 1, DISB_ADJ).
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'RGDWDEV' cannot be set to SQL_Latin1_General_CP1_CI_AS.
I do not know how to fix this.
Can anyone help me?
thank you, beth
I have created a stored procedure in msdb that will accept a database name and then rebuild non-clustered indexes in that database.
I placed the sp in msdb since msdb is where most job-related information is stored.
The sp identifies non-clustered indexes whose avg fragmentation exceeds a pre-determined threshold, and then runs the following statement for each index in the resulting subset:
SET @str_sql = 'ALTER INDEX ' + @str_index + ' ON ' + @str_object + ' REBUILD '
EXEC sp_executesql @str_sql
where @str_index is the name of the index and @str_object is the name of the object (view, table).
Clearly, I need to revise the statement so that it refers to the appropriate database.
Is there a way to execute 'alter index ... rebuild' so that it points to a database other than the current?
Thanks.
Hi.
I'm very new to DB stuff. I've created a SQL Server CE database and I'm trying to implement a query notification using a SqlDependency object, as per the instructions at:
http://www.codeproject.com/cs/database/chatter.asp
I've connected to my DB using SQL Server Management Studio Express, and I'm getting the following error when trying to execute an ALTER DATABASE command:
I have no clue what I could be doing wrong. Is this operation supported using CE? Is there really something wrong with my syntax?
Major Error 0x80040E14, Minor Error 25501
> alter database GESim.Diagnostics set ENABLE_BROKER
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = database ]
Help appreciated...
Just Mike
Hi,
I'm trying to change the file extension of one of the database files of a big database that contains 10 years of EPOS data.
When this file was created, at the beginning of 2008, with the following script:
USE [Live]
ALTER DATABASE [Live] ADD FILE (NAME = 'Live_2008',
FILENAME = €˜E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataLive_2008') TO FILEGROUP [2008]
the .ndf file extension was not added to the filename. So the file now has a file type of "File", instead of "SQL Server Secondary Data File"
I tried, in a test environment, to bring the database offline, change the filename to "Live_2008.ndf" and bring the database back online, but it gives me an error message saying that "cannot open the Live_2008 file", i'm guessing because it's looking for "Live_2008" and it finds "Live_2008.ndf" instead.
This file is quite big (5Gb) and I cannot afford to risk to lose any data.
The situation is complicated even further by the fact that it seems that a partition for 2008 does not exist.
How can I find out what data this file contains?
Is it going to be an issue if I leave the file like that?
Thanks
I have database1 and database2. Most table names are the same between both dbs.
I would like to copy a column from db1 into db2 but since the data requirement on db2 is different i would like to change it before inserting.
Example:
Data from db1: <SomePage id="1" <template>This is a test </template>
When it saves to db2 it needs to beÂ
<Page ident="1" <content>This is a test </content>
Is this possible at all?Â
I am trying to execute the following query , in Management Studio. But it takes forever. Can someone tell me why is this happening? I am running the query in 'NorthWind' database.The windows account under which I am logged into WinXP (windows authentication is enabled for the SQL Server database) is the database owner for NorthWind database.
alter database NorthWind SET ENABLE_BROKER
I'm attempting to alter a database by removing one of two log files. I have truncated both log files successfully and the database has no processes but I get an error stating file cannot be removed because it is not empty. Help
View 2 Replies View RelatedHi folks,
I do not have a backup of the model database and have had to rebuild SQL Server 2000 once already. All my databases except model and tempdb have the collation SQL_Latin1_General_CP1_CI_AS those two have Latin1_General_CI_AS.
During SQL rebuild I've restored over master and msdb and they work fine. Only came across problem with stored procedure and I'm lost as to how to alter model without a backup of it.
Any easy way to change model collation?
The command listed in subject fails as it is a system table.
Is it as simple as going into single user mode and trying that command again?
Help please this is day 3 of my little nightmare.