Help with update forms: databases

Showing posts with label databases. Show all posts

Friday, March 30, 2012

Help!!

Hi,
I'm new to SQL Server. One of our databases is very slow. Need to look into it. Any ideas?
Thanks!!Run some of your frequently used queries though the query analyser and look at the execution plan. This will give you a break down of how much time is being used in each step of the query and should give an indication of where you should start looking to improve performance.|||Start with the profiler! Check for

- long running queries
- locks and the duration thereof
- scans

Then you can start using the Query Analyzer. Add (nolock) where appropriate, index your tables and if you have ANY in-line SQL with variable where statements or more than one table then you should immediately move those to stored procedures.|||As rokslide said.. you can check the Execution Plan.. but.. if you have done it.. first you must create all the indexes and the primary keys in your tables.

Wednesday, March 28, 2012

HELP! Transaction log huge, & I'm out of space!

I have several .ldf files for one of my databases and one
of them is taking up 23 gigs. I really dont need to save
the transaction logs so I would like to just delete them
and start with a smaller log file. We created another
log file on c: now just so we can keep things running but
that is going to fill up very soon as well.
Is there any ways I can just delete the all the
transaction logs and just create one that is very small?
When I try to delete them I get an error message that
says I cant delete the file because it is not empty.
When I take the database offline to delete the file I
cant do that either because I cant access the database.
Any suggestions?Two things:
A) Put your databases into Simple Recovery mode. They're probably in
Full Recovery mode now, which is why your logs are growing so quickly.
B) To truncate the logs, run:
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
Then use sp_helpfile to get the file ID of the log file(s), and for
each, run:
DBCC SHRINKFILE(<FileID>, EMPTYFILE)
This should drop any space available back to the operating system.
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Hi,
Detach the database. Then delete the .ldf (rename it to be on the safe
side). Then reattach the database. It will create a new .ldf. Once this
is achieved, you can permanently delete that .ldf.
If you never need the .ldf, change the recovery model to simple.
HTH,
Bernie Yaeger
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||You can't attach a database with multiple log files using
sp_attach_single_file_db. The method Adam suggested is the right approach
to remove unneeded log files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
> Hi,
> Detach the database. Then delete the .ldf (rename it to be on the safe
> side). Then reattach the database. It will create a new .ldf. Once this
> is achieved, you can permanently delete that .ldf.
> If you never need the .ldf, change the recovery model to simple.
> HTH,
> Bernie Yaeger
> "Groundid" <groundid@.comcast.net> wrote in message
> news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
>> I have several .ldf files for one of my databases and one
>> of them is taking up 23 gigs. I really dont need to save
>> the transaction logs so I would like to just delete them
>> and start with a smaller log file. We created another
>> log file on c: now just so we can keep things running but
>> that is going to fill up very soon as well.
>> Is there any ways I can just delete the all the
>> transaction logs and just create one that is very small?
>> When I try to delete them I get an error message that
>> says I cant delete the file because it is not empty.
>> When I take the database offline to delete the file I
>> cant do that either because I cant access the database.
>> Any suggestions?
>|||Hi Dan,
If you either delete or rename (at the DOS level) the log file, you will not
be attaching a database with multiple log files.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7raWMjjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> You can't attach a database with multiple log files using
> sp_attach_single_file_db. The method Adam suggested is the right approach
> to remove unneeded log files.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
> > Hi,
> >
> > Detach the database. Then delete the .ldf (rename it to be on the safe
> > side). Then reattach the database. It will create a new .ldf. Once
this
> > is achieved, you can permanently delete that .ldf.
> >
> > If you never need the .ldf, change the recovery model to simple.
> >
> > HTH,
> >
> > Bernie Yaeger
> >
> > "Groundid" <groundid@.comcast.net> wrote in message
> > news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> >> I have several .ldf files for one of my databases and one
> >> of them is taking up 23 gigs. I really dont need to save
> >> the transaction logs so I would like to just delete them
> >> and start with a smaller log file. We created another
> >> log file on c: now just so we can keep things running but
> >> that is going to fill up very soon as well.
> >>
> >> Is there any ways I can just delete the all the
> >> transaction logs and just create one that is very small?
> >> When I try to delete them I get an error message that
> >> says I cant delete the file because it is not empty.
> >> When I take the database offline to delete the file I
> >> cant do that either because I cant access the database.
> >> Any suggestions?
> >
> >
>|||"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> If you either delete or rename (at the DOS level) the log file, you will
not
> be attaching a database with multiple log files.
Bernie,
To understand what Dan is talking about (and to get a glimpse into how I
once destroyed a weeks' worth of work and decided to never again manually
delete the log file):
Create a database with two log files. Detach it. Delete one (or both)
of the log files. Now try to re-attach it.|||Hi groundid,
Dont delete, just shrink the file. Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Idetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
4. Check the current file size by execution
DBCC SQLPERF(LOGSPACE)
Have a look into the below article on shrink file,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Note:
Please perform a full database backup after performing the above steps, as
well as schedule a transaction log backup frequently based on ur
requirement.
Thanks
Hari
MCDBA
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Bernie, try the script below.
CREATE DATABASE TwoLogFiles ON
(NAME='TwoLogFiles',
FILENAME='C:\TwoLogFiles.mdf')
LOG ON
(NAME='TwoLogFiles_Log1',
FILENAME='C:\TwoLogFiles_Log1.ldf'),
(NAME='TwoLogFiles_Log2',
FILENAME='C:\TwoLogFiles_Log2.ldf')
EXEC sp_detach_db 'TwoLogFiles'
EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
EXEC sp_attach_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf',
'C:\TwoLogFiles_Log1.ldf'
EXEC sp_attach_single_file_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> Hi Dan,
> If you either delete or rename (at the DOS level) the log file, you will
> not
> be attaching a database with multiple log files.
> Bernie
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> Bernie, try the script below.
> CREATE DATABASE TwoLogFiles ON
> (NAME='TwoLogFiles',
> FILENAME='C:\TwoLogFiles.mdf')
> LOG ON
> (NAME='TwoLogFiles_Log1',
> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> (NAME='TwoLogFiles_Log2',
> FILENAME='C:\TwoLogFiles_Log2.ldf')
> EXEC sp_detach_db 'TwoLogFiles'
> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> EXEC sp_attach_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf',
> 'C:\TwoLogFiles_Log1.ldf'
> EXEC sp_attach_single_file_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> > Hi Dan,
> >
> > If you either delete or rename (at the DOS level) the log file, you will
> > not
> > be attaching a database with multiple log files.
> >
> > Bernie
> >
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23k44GyjjEHA.1136@.tk2msftngp13.phx.gbl...
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> >
> > If you either delete or rename (at the DOS level) the log file, you will
> not
> > be attaching a database with multiple log files.
> Bernie,
> To understand what Dan is talking about (and to get a glimpse into how
I
> once destroyed a weeks' worth of work and decided to never again manually
> delete the log file):
> Create a database with two log files. Detach it. Delete one (or
both)
> of the log files. Now try to re-attach it.
>|||Bernie,
> Why are you guys talking about 2 transaction logs? This guy has one.
Here's a quote from the OP:
"I have several .ldf files for one of my databases and one
of them is taking up 23 gigs."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/|||> Why are you guys talking about 2 transaction logs? This guy has one.
As Tibor mentioned, the OP has multiple logs.
The procedure you suggest out to work with a single log file but is not the
preferred method. Even with a single log, the best approach to reduce log
file size is DBCC SHRINKFILE. This doesn't require the database to be
detached.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:MiFYc.18971$Es2.8127856@.news4.srv.hcvlny.cv.net...
> Hi Dan, Adam,
> Why are you guys talking about 2 transaction logs? This guy has one.
> Here's a process I've used countless times and haven't experienced any
> problem:
> 1.. right click the database in em;
> 2.. select all tasks;
> 3.. select detach database;
> 4.. proceed to detach;
> 5.. delete the .ldf at the DOS level;
> 6.. right click the 'databases' line beneath the server in em; all tasks;
> attach the .mdf (browse for it to the appropriate sub);
> 7.. it will automatically create its own blank transaction log;
> I am using the simple recovery model, but I don't know if this makes any
> difference.
> Now tell me why this doesn't work in your opinion and explain to me why it
> always works for me (SQL Server 2000).
> Bernie
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Bernie, try the script below.
>> CREATE DATABASE TwoLogFiles ON
>> (NAME='TwoLogFiles',
>> FILENAME='C:\TwoLogFiles.mdf')
>> LOG ON
>> (NAME='TwoLogFiles_Log1',
>> FILENAME='C:\TwoLogFiles_Log1.ldf'),
>> (NAME='TwoLogFiles_Log2',
>> FILENAME='C:\TwoLogFiles_Log2.ldf')
>> EXEC sp_detach_db 'TwoLogFiles'
>> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
>> EXEC sp_attach_db
>> 'TwoLogFiles',
>> 'C:\TwoLogFiles.mdf',
>> 'C:\TwoLogFiles_Log1.ldf'
>> EXEC sp_attach_single_file_db
>> 'TwoLogFiles',
>> 'C:\TwoLogFiles.mdf'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
>> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
>> > Hi Dan,
>> >
>> > If you either delete or rename (at the DOS level) the log file, you
>> > will
>> > not
>> > be attaching a database with multiple log files.
>> >
>> > Bernie
>> >
>>
>|||Hi Tibor,
You're correct - I misread his question. Tx
Bernie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23l2Sv9ojEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Bernie,
> > Why are you guys talking about 2 transaction logs? This guy has one.
> Here's a quote from the OP:
> "I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs."
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||Hi Dan,
You're correct - I misread the initial question. Sorry.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ewXt$lpjEHA.2788@.tk2msftngp13.phx.gbl...
> > Why are you guys talking about 2 transaction logs? This guy has one.
> As Tibor mentioned, the OP has multiple logs.
> The procedure you suggest out to work with a single log file but is not
the
> preferred method. Even with a single log, the best approach to reduce log
> file size is DBCC SHRINKFILE. This doesn't require the database to be
> detached.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:MiFYc.18971$Es2.8127856@.news4.srv.hcvlny.cv.net...
> > Hi Dan, Adam,
> >
> > Why are you guys talking about 2 transaction logs? This guy has one.
> > Here's a process I've used countless times and haven't experienced any
> > problem:
> > 1.. right click the database in em;
> > 2.. select all tasks;
> > 3.. select detach database;
> > 4.. proceed to detach;
> > 5.. delete the .ldf at the DOS level;
> > 6.. right click the 'databases' line beneath the server in em; all
tasks;
> > attach the .mdf (browse for it to the appropriate sub);
> > 7.. it will automatically create its own blank transaction log;
> > I am using the simple recovery model, but I don't know if this makes any
> > difference.
> >
> > Now tell me why this doesn't work in your opinion and explain to me why
it
> > always works for me (SQL Server 2000).
> >
> > Bernie
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> >> Bernie, try the script below.
> >>
> >> CREATE DATABASE TwoLogFiles ON
> >> (NAME='TwoLogFiles',
> >> FILENAME='C:\TwoLogFiles.mdf')
> >> LOG ON
> >> (NAME='TwoLogFiles_Log1',
> >> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> >> (NAME='TwoLogFiles_Log2',
> >> FILENAME='C:\TwoLogFiles_Log2.ldf')
> >>
> >> EXEC sp_detach_db 'TwoLogFiles'
> >>
> >> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> >>
> >> EXEC sp_attach_db
> >> 'TwoLogFiles',
> >> 'C:\TwoLogFiles.mdf',
> >> 'C:\TwoLogFiles_Log1.ldf'
> >>
> >> EXEC sp_attach_single_file_db
> >> 'TwoLogFiles',
> >> 'C:\TwoLogFiles.mdf'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> >> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> >> > Hi Dan,
> >> >
> >> > If you either delete or rename (at the DOS level) the log file, you
> >> > will
> >> > not
> >> > be attaching a database with multiple log files.
> >> >
> >> > Bernie
> >> >
> >>
> >>
> >
> >
>

HELP! Synchronize db and maintain foriegn key relations?

I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql.

Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

locally the following products are added:

11001 short sleeve t--gets added with primary key in 'item' table 'id' of '7'

11002 long sleeve t--gets added with primary key in 'item' table 'id' '8'

remotely the following products are added:

21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

31001 overalls--gets added with primary key in 'item' table 'id' '8'

remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

product with "item_id" of '7' is "short sleeve t".

9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.Sounds like a nice problem :p
Never had this one but it's fun to think about it, so this is what I came up with:

Use different ranges... Set the IDENTITY (or AUTO_INCREMENT) on MySQL on a very high number, one you won't expect to reach in this product life cycle. Let say 10000000.

When synchronizing set the IDENTITY_INSERT ON, insert the records from the other server. Set IDENTITY_INSERT OFF, reseed to the lower value and continue.

Example:
CREATE TABLE tst (c1 INT IDENTITY, c2 INT)

INSERT tst(c2) VALUES (1)
INSERT tst(c2) VALUES (2)
INSERT tst(c2) VALUES (3)
INSERT tst(c2) VALUES (4)

SET IDENTITY_INSERT tst ON

INSERT tst(c1, c2) VALUES (10000000, 5)
INSERT tst(c1, c2) VALUES (10000001, 6)
INSERT tst(c1, c2) VALUES (10000002, 7)

SET IDENTITY_INSERT tst OFF

DECLARE @.i INT
SELECT @.i = MAX(c1) FROM tst WHERE c1 < 10000000
DBCC CHECKIDENT ('tst', RESEED, @.i)

INSERT tst(c2) VALUES (8)
INSERT tst(c2) VALUES (9)
INSERT tst(c2) VALUES (10)

SELECT * FROM tst

DROP TABLE tst
Ofcourse this could be useless in your case, 'cause what happens when someone is inserting while you're synchronizing!? But maybe it's a start.|||or else u can use another set of tables for remote data and use views to combine both local & remote data. u might be needing an additional flag field in the view to identify the source.

HELP! Synchronize db and maintain foreign key relations?

Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

locally the following products are added:

11001 short sleeve t--gets added with primary key in 'item' table 'id' of '7'

11002 long sleeve t--gets added with primary key in 'item' table 'id' '8'

remotely the following products are added:

21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

31001 overalls--gets added with primary key in 'item' table 'id' '8'

remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

product with "item_id" of '7' is "short sleeve t".

9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.Hi,

The way i'm using is to create my own table with two columns...

The columns are Old_ID and New_ID

Before syncronization u inserts all elements from the synchronized table there!

After appending ur data into the main table u need to identify using other field /s/ which id corresponds to ur ancient id /with Update action query

The last step is appending the info from the table with foreign keys linked with ur temporary table. Instaed using the old foreign key u need to put the new entry and the information will be at its place!

Hope this helps as conception!

Help! Suspect Databases - Hardware Failure

We have numerous suspect databases across multiple serverse due to a hardware
failure on our SAN. The part of the SAN that failed had a combination of data
and log files, but not both for a given database.
Right now we have services shut down on the server. When we bring the server
back up, after fixing our hardware failure, what would be the best steps?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6eca09c69ec9c@.uwe...
> We have numerous suspect databases across multiple serverse due to a
> hardware
> failure on our SAN. The part of the SAN that failed had a combination of
> data
> and log files, but not both for a given database.
> Right now we have services shut down on the server. When we bring the
> server
> back up, after fixing our hardware failure, what would be the best steps?
BEFORE bringing up the server, make complete backups of all files.
Then once you bring up the server, check the logs and find out why the
databases are suspect.
In some cases it may be as simple as the drive letters or paths no longer
being right and once you fix those, the database may come up cleanly.
If it's more complex, you'll have to do different things, but hard to
recommend w/o knowing in advance the exact nature of the problems.
No matter what I'd make sure to do a DBCC checkdb once the database is back
up, just to be sure.
(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
2000/2005 database, so you may luck out.)

> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Thanks Greg. Once the hardware was repaired and we restarted the services (we
had complete backups available) all databases came up cleanly.
If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
hours, if there were errors encountered, would I still be informed, even
though I had the "WITH NO_INFOMSGS" option?
Greg D. Moore (Strider) wrote:
>[quoted text clipped - 5 lines]
>BEFORE bringing up the server, make complete backups of all files.
>Then once you bring up the server, check the logs and find out why the
>databases are suspect.
>In some cases it may be as simple as the drive letters or paths no longer
>being right and once you fix those, the database may come up cleanly.
>If it's more complex, you'll have to do different things, but hard to
>recommend w/o knowing in advance the exact nature of the problems.
>No matter what I'd make sure to do a DBCC checkdb once the database is back
>up, just to be sure.
>(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
>2000/2005 database, so you may luck out.)
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6ecae312206bc@.uwe...
> Thanks Greg. Once the hardware was repaired and we restarted the services
> (we
> had complete backups available) all databases came up cleanly.
>
Glad to hear it.

> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run
> after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
I believe so.

>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Help! Suspect Databases - Hardware Failure

We have numerous suspect databases across multiple serverse due to a hardware
failure on our SAN. The part of the SAN that failed had a combination of data
and log files, but not both for a given database.
Right now we have services shut down on the server. When we bring the server
back up, after fixing our hardware failure, what would be the best steps?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6eca09c69ec9c@.uwe...
> We have numerous suspect databases across multiple serverse due to a
> hardware
> failure on our SAN. The part of the SAN that failed had a combination of
> data
> and log files, but not both for a given database.
> Right now we have services shut down on the server. When we bring the
> server
> back up, after fixing our hardware failure, what would be the best steps?
BEFORE bringing up the server, make complete backups of all files.
Then once you bring up the server, check the logs and find out why the
databases are suspect.
In some cases it may be as simple as the drive letters or paths no longer
being right and once you fix those, the database may come up cleanly.
If it's more complex, you'll have to do different things, but hard to
recommend w/o knowing in advance the exact nature of the problems.
No matter what I'd make sure to do a DBCC checkdb once the database is back
up, just to be sure.
(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
2000/2005 database, so you may luck out.)
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Thanks Greg. Once the hardware was repaired and we restarted the services (we
had complete backups available) all databases came up cleanly.
If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
hours, if there were errors encountered, would I still be informed, even
though I had the "WITH NO_INFOMSGS" option?
Greg D. Moore (Strider) wrote:
>> We have numerous suspect databases across multiple serverse due to a
>> hardware
>[quoted text clipped - 5 lines]
>> server
>> back up, after fixing our hardware failure, what would be the best steps?
>BEFORE bringing up the server, make complete backups of all files.
>Then once you bring up the server, check the logs and find out why the
>databases are suspect.
>In some cases it may be as simple as the drive letters or paths no longer
>being right and once you fix those, the database may come up cleanly.
>If it's more complex, you'll have to do different things, but hard to
>recommend w/o knowing in advance the exact nature of the problems.
>No matter what I'd make sure to do a DBCC checkdb once the database is back
>up, just to be sure.
>(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
>2000/2005 database, so you may luck out.)
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6ecae312206bc@.uwe...
> Thanks Greg. Once the hardware was repaired and we restarted the services
> (we
> had complete backups available) all databases came up cleanly.
>
Glad to hear it.
> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run
> after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
I believe so.
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||On 06.03.2007 22:11, cbrichards via SQLMonster.com wrote:
> Thanks Greg. Once the hardware was repaired and we restarted the services (we
> had complete backups available) all databases came up cleanly.
> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
Wouldn't it be better to run this first before using the DB again? I
mean if there were issues you'd rather want to fix them before you put
the DB back in production.
Kind regards
robert

Help! Suspect Databases - Hardware Failure

We have numerous suspect databases across multiple serverse due to a hardwar
e
failure on our SAN. The part of the SAN that failed had a combination of dat
a
and log files, but not both for a given database.
Right now we have services shut down on the server. When we bring the server
back up, after fixing our hardware failure, what would be the best steps?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6eca09c69ec9c@.uwe...
> We have numerous suspect databases across multiple serverse due to a
> hardware
> failure on our SAN. The part of the SAN that failed had a combination of
> data
> and log files, but not both for a given database.
> Right now we have services shut down on the server. When we bring the
> server
> back up, after fixing our hardware failure, what would be the best steps?
BEFORE bringing up the server, make complete backups of all files.
Then once you bring up the server, check the logs and find out why the
databases are suspect.
In some cases it may be as simple as the drive letters or paths no longer
being right and once you fix those, the database may come up cleanly.
If it's more complex, you'll have to do different things, but hard to
recommend w/o knowing in advance the exact nature of the problems.
No matter what I'd make sure to do a DBCC checkdb once the database is back
up, just to be sure.
(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
2000/2005 database, so you may luck out.)

> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Thanks Greg. Once the hardware was repaired and we restarted the services (w
e
had complete backups available) all databases came up cleanly.
If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
hours, if there were errors encountered, would I still be informed, even
though I had the "WITH NO_INFOMSGS" option?
Greg D. Moore (Strider) wrote:
>[quoted text clipped - 5 lines]
>BEFORE bringing up the server, make complete backups of all files.
>Then once you bring up the server, check the logs and find out why the
>databases are suspect.
>In some cases it may be as simple as the drive letters or paths no longer
>being right and once you fix those, the database may come up cleanly.
>If it's more complex, you'll have to do different things, but hard to
>recommend w/o knowing in advance the exact nature of the problems.
>No matter what I'd make sure to do a DBCC checkdb once the database is back
>up, just to be sure.
>(btw, I've found that it takes pretty bad problems to outright corrupt a SQ
L
>2000/2005 database, so you may luck out.)
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6ecae312206bc@.uwe...
> Thanks Greg. Once the hardware was repaired and we restarted the services
> (we
> had complete backups available) all databases came up cleanly.
>
Glad to hear it.

> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run
> after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
I believe so.

>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||On 06.03.2007 22:11, cbrichards via droptable.com wrote:
> Thanks Greg. Once the hardware was repaired and we restarted the services
(we
> had complete backups available) all databases came up cleanly.
> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run aft
er
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
Wouldn't it be better to run this first before using the DB again? I
mean if there were issues you'd rather want to fix them before you put
the DB back in production.
Kind regards
robertsql

HELP! Subscription obsolete and Data NOT sychronized!

We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasn=B4t detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
Andreas, you could manually synchronize the data using datacompare from
Redgate then do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison schrieb:

> Andreas, you could manually synchronize the data using datacompare from
> Redgate then do a nosync initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
But SQL DataCompare can either copy from database A to B or from B to
A, but cannot merge, or am I wrong?
And I a dataset exists on site A, but not on B, does that mean, that
the dataset was new on A and should copy to B, or was the dataset
delete on B and should also be deleted on A?
|||there is no way in Merge, there is a way in transactional but its dangerous.
If you do reinitialize it will prompt you to upload changes from the
subscriber first which will send the subscribers changes to the publisher.
I think you need to investigate 1) what is causing the problem which makes
your subscriber go offline 2) what is causing these errors you get after
running the snapshot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128770684.895595.285420@.g44g2000cwa.googlegr oups.com...
We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasnt detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
|||Andreas - you're quite right. To get this to work, you'll have to select
which one is the master in terms of updates to the same record on both
sites. If the updates are partitioned, then you can use this tool more
easily, but if there are a lot of conflicts then there is a big problem to
sort out, and only you can decide what is appropriate ie who wins these
conflicts, or in other words, which node is the master.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hillary,
thank you for your answer, but I don=B4t understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
|||Unfortunately there is no master. Each of the 3 databases have the same
priority.
I cannot set anything aside.
What about my question:[vbcol=seagreen]
Do you think there is a chance to get a system which synchronizes the
data, without marking the subscibers as "obsolete"?
|||What do you think about this scenario:
1) Deleting the subscriptions
2) New pull Merge Subscriptions => Replication will run again, but with
different data
3) Restore the subscibers databases, state before deleting the
subscriptions
Do you think this will work to synchronize the databases?
If this works, maybe the changes from the publisher were not
transmittet to the subscriters yet, and I have to backup the subscribes
database and restore it as the subscribers database.
MAY THIS WORK?
|||Andreas,
this sounds feasible. You'll probably also need to change the value of the
status flag in sysmergesubscriptions. Alternatively (and probably easier)
you might be able to increase the activation/retention period and change
this flag. I haven't tried either approach and don't have any expired
subscriptions to test, so please let us know how you get on.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, with merge replication when you reinitialize you are prompted to upload
the changes which happened on your subscribers first.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128924652.854174.261410@.g47g2000cwa.googlegr oups.com...
Hillary,
thank you for your answer, but I dont understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?

Monday, March 26, 2012

Help! SQLExpress2005 date formats

I installed SQLExpress2005 and restored some old MSSQL7 databases. This
seemed to work at first glance, but there is at least one problem. These
databases contained datetime values.
And we are in Germany where dates are written like this: 25.12.2005
(christmas, for example).
My ColdFusion application contains queries like this:
SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
Notiz
FROM tblBetriebskalender
WHEREdatum between '01.12.2005' and '31.12.2005'
ORDER BYDatum
this used to work without problems.
Now the same queries don't work because for a date such as '31.12.2005',
SQLServer thinks 12 is the day and 31 is the month.
What can I do? Is there a central switch somewhere that changes the data
formats? Unfortunately I don't know what it was set to in the old system.
(The database properties option tab shows that collation is now set to
SQL_Latin1_General_CP1_CI_AS, and the compatibility level is SQLServer 7.0.)
-Michael
hi Michael,
Michael Peters wrote:
> I installed SQLExpress2005 and restored some old MSSQL7 databases.
> This seemed to work at first glance, but there is at least one
> problem. These databases contained datetime values.
> And we are in Germany where dates are written like this: 25.12.2005
> (christmas, for example).
> My ColdFusion application contains queries like this:
> SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
> Notiz
> FROM tblBetriebskalender
> WHERE datum between '01.12.2005' and '31.12.2005'
> ORDER BY Datum
> this used to work without problems.
> Now the same queries don't work because for a date such as
> '31.12.2005', SQLServer thinks 12 is the day and 31 is the month.
> What can I do? Is there a central switch somewhere that changes the
> data formats? Unfortunately I don't know what it was set to in the
> old system.
> (The database properties option tab shows that collation is now set to
> SQL_Latin1_General_CP1_CI_AS, and the compatibility level is
> SQLServer 7.0.)
> -Michael
Dates should be handled in ISO format YYYY-MM-DD or YYYYMMDD in order to
avoid this kind of problem as the dateformat is language specific and each
login has na associated language default.. probably you had german setting
for your login on the old server and englis setting on the new one...
you can change the login's language
ALTER LOGIN login_name
DEFAULT_LANGUAGE = new_language
http://msdn2.microsoft.com/en-us/library/ms189828.aspx
or, at connection level, using the SET DATEFORMAT
http://msdn.microsoft.com/library/de...-set_052s.asp,
but I strongly urge you to modify your language management to standard
ISO/ODBC format..
an unvaluable article by Notre Dame SQL Server Kalen Delaney about "dates"
is available at
http://www.windowsitpro.com/SQLServe...147/9147.html, but
only for subscriber...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks very much Andrea.
-Michael

help! SQL server trial expired

My SQL Server trial has expired and now I can't seem to access my databases,
is there anyway to import them into MSDE so I can still access them?
You can simply attach user databases to MSDE using sp_attach_db. See BOL for
syntax. This assumes they don't break the file size limits of MSDE which I
believe is 2 GB. An example below
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Data\pubs.mdf',
@.filename2 = N'c:\Data\pubs_log.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skylar Challand" <skylar.challand@.blatant.ca> wrote in message
news:OnrhQ9jIEHA.3444@.TK2MSFTNGP11.phx.gbl...
> My SQL Server trial has expired and now I can't seem to access my
databases,
> is there anyway to import them into MSDE so I can still access them?
>
sql

help! SQL server trial expired

My SQL Server trial has expired and now I can't seem to access my databases,
is there anyway to import them into MSDE so I can still access them?You can simply attach user databases to MSDE using sp_attach_db. See BOL for
syntax. This assumes they don't break the file size limits of MSDE which I
believe is 2 GB. An example below
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Data\pubs.mdf',
@.filename2 = N'c:\Data\pubs_log.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skylar Challand" <skylar.challand@.blatant.ca> wrote in message
news:OnrhQ9jIEHA.3444@.TK2MSFTNGP11.phx.gbl...
> My SQL Server trial has expired and now I can't seem to access my
databases,
> is there anyway to import them into MSDE so I can still access them?
>

help! SQL server trial expired

My SQL Server trial has expired and now I can't seem to access my databases,
is there anyway to import them into MSDE so I can still access them?You can simply attach user databases to MSDE using sp_attach_db. See BOL for
syntax. This assumes they don't break the file size limits of MSDE which I
believe is 2 GB. An example below
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Data\pubs.mdf',
@.filename2 = N'c:\Data\pubs_log.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skylar Challand" <skylar.challand@.blatant.ca> wrote in message
news:OnrhQ9jIEHA.3444@.TK2MSFTNGP11.phx.gbl...
> My SQL Server trial has expired and now I can't seem to access my
databases,
> is there anyway to import them into MSDE so I can still access them?
>

Friday, March 23, 2012

Help! Restarting the MSSQLSERVER service.

Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?No, restarting an SQL Server service shouldn't destroy databases. What made
you ask this?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?|||SQL Server is designed to go through stop/start cycles without losing any
information. There may be issues within a database that a stop/start cycle
will catch, but the process itself should not cause any problems. If you
have a very long running transaction it may take a while for the recovery
process to complete. If you have underlying hardware issues, a stop/start
can fail.
Why do you find it necessary to stop and restart SQL Server so often? I
seldom restart the service, typically only when I have to reboot the host
computer for an OS patch. I suspect that with a bit of tuning, tewaking,
and monitoring, you can get much better reliability and availability than
you have right now.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi,
> if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this
> was
> the case. I restart sometimes to get the databases back online and
> running.
> Am I totally wrong here?|||Hi
Stopping SQL Server using Control Panel or Tray Icon or EM results in an
orderly shutdown.
Starting it again then starts the process of recovery, with incomplete
transactions being rolled back. If a database is corrupt, it may detect it
during startup too and will result in the DB being marked as 'suspect'.
What do you understand as "running database". A Process runs, not a database.
If you are exeriencing intermittent problems like queries timing out, then
run sp_who2 to see what is happening, as restarting SQL Server will drop the
connections and you still do not find the casue of your blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Horner" wrote:
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?|||My CTO told me that I almost destroyed several production databases by my
actions...
I thank you for your answer and thought anyways that I hadn't lost my mind.
"Narayana Vyas Kondreddi" wrote:
> No, restarting an SQL Server service shouldn't destroy databases. What made
> you ask this?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?
>
>|||Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> > Can someone please tell me, perferrably an official Microsoft SQL profi,
> > if
> > restarting the mssqlserver service could destroy any running databases?
> > I thought that logically, this wouldn't be able to be restarted if this
> > was
> > the case. I restart sometimes to get the databases back online and
> > running.
> > Am I totally wrong here?
>
>|||Restart the service shouldn't be the solution. I know you are not the DBA,
but you should let your CTO know that the database needs tuning. I don't
restart SQL Server service for months, and when I do restart that'll be for
a software update or hardware maintenance etc.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:A520E89D-833C-4E0F-BD63-663C2290755A@.microsoft.com...
Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start
cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in
message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> > Can someone please tell me, perferrably an official Microsoft SQL profi,
> > if
> > restarting the mssqlserver service could destroy any running databases?
> > I thought that logically, this wouldn't be able to be restarted if this
> > was
> > the case. I restart sometimes to get the databases back online and
> > running.
> > Am I totally wrong here?
>
>

help! report users permission on a SQL 2000 server

hi
There are over 10 databases on my SQL server. I would like to have a report
that shows the user's permission on each database including tables, views an
d
stored procedures. At this stage, I want to know which user can run or not
stored procedures on each database. Is it possible to find permission using
system stored procedures? I read the online book of SQL server for 3 days.
But I still don't find the system stored procedures.
If this stored produres doesn't exist, I have to write a stored proceduce to
display these user's permission. Could you tell which system tables need to
be used?
Waiting for you
Million thanks in adv
Wen OUYou can use sp_helprotect, it is ran as follows:
use db1
sp_helprotect @.username = 'Username'
use db2
sp_helprotect @.username = 'Username'|||thank you for your reply
if there are more than 1 database, i have to use a cursor to access each
database and read the info.
do you have another idea?
waiting for you
thanks in adv
Wen OU
"Fany Vargas" wrote:

> You can use sp_helprotect, it is ran as follows:
> use db1
> sp_helprotect @.username = 'Username'
> use db2
> sp_helprotect @.username = 'Username'
>|||If you need to get this info for each database then you will indeed need to
loop through each database. So using a cursor to loop through each database
is a good option.
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||thank you
I have another question. If a domain user belongs to a domain user group, is
it possible to find if this user get permissions to execute a stored
procedure? At the moment, the stored procedure i wrote only shows the
permisssion of user groups or independent users. what system stored
procedure can I use?
million thanks in adv
wen ou
"Fany Vargas [MSFT]" wrote:

> If you need to get this info for each database then you will indeed need t
o
> loop through each database. So using a cursor to loop through each databas
e
> is a good option.
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>|||Look into the PERMISSIONS function. It returns a value containing a bitmap
that indicates the statement, object, or column permissions for the current
user. So you can run something like:
SELECT PERMISSIONS(OBJECT_ID('nameofobject'))
For details on how to decipher the bitmap you will need to reference BOL
article: "PERMISSIONS"
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ts
qlref.chm::/ts_pa-pz_6f78.htm) - in BOL, select Go->Url...
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||Also look at the BOL topic: SETUSER
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||thank you for your help
it's very useful for me
"Fany Vargas [MSFT]" wrote:

> Also look at the BOL topic: SETUSER
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>sql

Wednesday, March 21, 2012

Help! Merge Replication and identity values

Hi,
I have a Merge replication set up between a SQL Server and many (around
150-200) SQL CE databases. I am using identity as primary keys in 2 of the
replicated tables which have a identity seed (set to 1) and range set (set
to 1000 with 10000 as publisher range and 80% threshold). All was going well
until there was a database schema change and I had to rebuild the
publication and re-initialize all subscribers (which was ok).
But after rebuilding the publication and re-initialization all subscribers,
the agent started giving out identity ranges that conflicted with current
values in the database. As some of you have faced similar situation and have
found workarounds. I used a script to manually change the "next seed" value
of the MSrepl_identity_range table to set the "next seed" identity value to
be the max value of the table...basically used UPDATE
distribution..MSrepl_identity_range SET next_seed = max value + range...so
on and so forth. This worked as far as giving each re-initialized
subscribers a new identity range. I have 2 questions regarding this:
* How can I set the publishers range? I manually updated the
MSrepl_identity_range to a higher value than the max value used for updating
the distribution..MSrepl_identity_range using the sql script. What effects
would this have?
* In Check Constraint tab of the replicated table (generating identity
values at subscriber), there is a check constraint value (value is:
[Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
for Insert and Update" with constraint name like
"repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
uncheck them for the merge agent to be able to add new rows to the server.
Where can I get more info on how this constraint value is generated and how
is it used? Also what is the harm leaving the enforcement of constraint
uncheck?
Can anyone point me to a website where someone has successfully dealt with
this issue without manually setting the ranges?
Please help.
Thanks.
wow! thats a lot of SQL CE databases.
1) to set the identity range on the publisher the correct way to do this is
through the articles property, select the identity range tab. Sounds like
you have already being there. After setting the range on the publisher the
ranges should be parceled out to the Subscribers. As Subscribers come online
they'll get a range assigned to them.
There are instances where the range won't be incremented correctly. For
instance if you have a range size on the publisher of 100 and you update
more than the threshold or range size on the publisher in a batch, the range
adjustment won't be done until the batch is complete. If the batch is more
than 100 records you blow the range and get a constraint error.
In cases like this you have to automatically adjust the identity ranges or
do it manually by adjusting the range table and the corresponding
constraint.
Because of these "limitations" many DBA's elect to use the set it and forget
it approach, where they assign a range to the publisher and subscriber
manually which will not be exceeded in the lifetime of the project/solution.
The dangers of manually making the adjustment is you have to use consistent
values everywhere and you have to adjust the constraint correctly. Other
than that its pretty safe.
The constraint is created when you create the snapshot and adjusted with the
proc sp_MSreseed. This proc is completely undocumented. If you disable the
constraint you may run into problems depending on what is updating your
table. Disableing it for inserts and updates will be harmless if only
replication is making the changes, otherwise you may have problems, if the
identity range is blown and another subscriber/publisher uses it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I have a Merge replication set up between a SQL Server and many (around
> 150-200) SQL CE databases. I am using identity as primary keys in 2 of the
> replicated tables which have a identity seed (set to 1) and range set (set
> to 1000 with 10000 as publisher range and 80% threshold). All was going
well
> until there was a database schema change and I had to rebuild the
> publication and re-initialize all subscribers (which was ok).
>
> But after rebuilding the publication and re-initialization all
subscribers,
> the agent started giving out identity ranges that conflicted with current
> values in the database. As some of you have faced similar situation and
have
> found workarounds. I used a script to manually change the "next seed"
value
> of the MSrepl_identity_range table to set the "next seed" identity value
to
> be the max value of the table...basically used UPDATE
> distribution..MSrepl_identity_range SET next_seed = max value + range...so
> on and so forth. This worked as far as giving each re-initialized
> subscribers a new identity range. I have 2 questions regarding this:
>
> * How can I set the publishers range? I manually updated the
> MSrepl_identity_range to a higher value than the max value used for
updating
> the distribution..MSrepl_identity_range using the sql script. What effects
> would this have?
> * In Check Constraint tab of the replicated table (generating identity
> values at subscriber), there is a check constraint value (value is:
> [Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
> for Insert and Update" with constraint name like
> "repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
> uncheck them for the merge agent to be able to add new rows to the server.
> Where can I get more info on how this constraint value is generated and
how
> is it used? Also what is the harm leaving the enforcement of constraint
> uncheck?
>
> Can anyone point me to a website where someone has successfully dealt with
> this issue without manually setting the ranges?
>
> Please help.
> Thanks.
>
>
|||Hilary,
Thanks a lot for your response. Googling on sp_MSreseed gives only one result by Fiach Reid who details the stored procedure itself. I will not be doing any batch inserts so I guess sql should handle the identity ranges automatically, but if it doesn't then I will start seeing conflicts on the server again and may be by that time I could have gathered more info on sp_MSreseed.
Thanks,
Deepak.
Hilary Cotter" <hilaryk@.att.net> wrote in message news:u$aeWOoUEHA.3420@.TK2MSFTNGP12.phx.gbl...

> wow! thats a lot of SQL CE databases.
> 1) to set the identity range on the publisher the correct way to do this is
> through the articles property, select the identity range tab. Sounds like
> you have already being there. After setting the range on the publisher the
> ranges should be parceled out to the Subscribers. As Subscribers come online
> they'll get a range assigned to them.
> There are instances where the range won't be incremented correctly. For
> instance if you have a range size on the publisher of 100 and you update
> more than the threshold or range size on the publisher in a batch, the range
> adjustment won't be done until the batch is complete. If the batch is more
> than 100 records you blow the range and get a constraint error.
> In cases like this you have to automatically adjust the identity ranges or
> do it manually by adjusting the range table and the corresponding
> constraint.
> Because of these "limitations" many DBA's elect to use the set it and forget
> it approach, where they assign a range to the publisher and subscriber
> manually which will not be exceeded in the lifetime of the project/solution.
> The dangers of manually making the adjustment is you have to use consistent
> values everywhere and you have to adjust the constraint correctly. Other
> than that its pretty safe.
> The constraint is created when you create the snapshot and adjusted with the
> proc sp_MSreseed. This proc is completely undocumented. If you disable the
> constraint you may run into problems depending on what is updating your
> table. Disableing it for inserts and updates will be harmless if only
> replication is making the changes, otherwise you may have problems, if the
> identity range is blown and another subscriber/publisher uses it.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
> news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> well
> subscribers,
> have
> value
> to
> updating
> how
>

Friday, March 30, 2012

Help!!

Wednesday, March 28, 2012

HELP! Transaction log huge, & I'm out of space!

HELP! Transaction log huge, & I'm out of space!

HELP! Transaction log huge, & I'm out of space!

HELP! Synchronize db and maintain foriegn key relations?

HELP! Synchronize db and maintain foreign key relations?

Help! Suspect Databases - Hardware Failure

Help! Suspect Databases - Hardware Failure

Help! Suspect Databases - Hardware Failure

HELP! Subscription obsolete and Data NOT sychronized!

Monday, March 26, 2012

Help! SQLExpress2005 date formats

help! SQL server trial expired

help! SQL server trial expired

help! SQL server trial expired

Friday, March 23, 2012

Help! Restarting the MSSQLSERVER service.

help! report users permission on a SQL 2000 server

Wednesday, March 21, 2012

Help! Merge Replication and identity values

Help with update forms

Blog Archive

About Me