Hi all,
I am new to SQL and was given a SQL server to administer. There are lots of databases on the server and no one seems to know which one is used and which one is not. I try to do some cleanning.
1. Can I find out if there is any activity on a database so I can delete them off and how?
2. There are backup jobs that run every night which do a full backup. I am thinking of changing it to do full backup once a week and differential every night. These databases are updated daily. Some have lots of activities, some have less. Does that sound reasonable? and if I do differential backup, should I create separate back up file or should I add on to the full backup file?
Any comments or suggestions are greatly appreciated.You can use perfmon, sp_who, "Current Activity" in enterprise manager and sql profiler (know that profiler is a performance consumer) to monitor database activity.
For backups, my question would be - is doing full backups taking too much time and/or requiring too much space ? If so, then do differentials. Are your backups to tape or disk ?|||look up sqlmaint.exe utility, it'll give you the flexibility and at the same time can help you standardize the retention period on disk for those backups. if backups don't take long, then i'd continue with full nightly, and do db health checks weekly, along with reindexing or updating stats. delete backups older than 2 or 1 days if you are short on disk, and do tape backup nightly of those backup files, and you'll be in good shape.
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Help with a loop to collect database names and tables within those databases.
I am trying to create a loop that will go though all the databases and
collect all the table names.
Currently the loop goes around and around till it scans all the
databases but only pull from the current database you are in.
I am probably missing something that is obvious.
DECLARE @.ExecSQLcmd VARCHAR(2048) -- Creates Storage Space for SQL
Command
DECLARE @.DBNum_to_Name INT -- Creates Storage Space for
Database Name
CREATE TABLE #tmp_RebuildIndexesPrameters (
DatabaseName NVARCHAR(128),
TableName CHAR(255),
DailyFragAmt INT, -- Maximum daily fragmentation to allow (30
is default)
DailyMaxRebuidAmt INT, -- Maximum index size durring the w
to do a rebuild instead of defrag (100 Default)
WlyFragAmt INT, -- Maximum wly fragmentation to allow (5
is default)
WlyMaxRebuidAmt INT, -- Maximum index size durring the
wend to do a rebuild instead of defrag (100 Default)
LastRun DATETIME,
Priority INT, -- User Defined varable. (-1 Never Run; 1-999,
Priority with 1 being the highest.)
Completed INT, -- Required to prevent looping based upon
Priority and LastRun.
)
/*********************************
Loop though all DBs by DBID Number
Notes: The collection of Database
names and Table names should take
place durring this loop. Once the
data has been collected, it can be
compared to the existing paramerers
**********************************/
SELECT @.DBNum_to_Name = min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes '
FROM master.dbo.sysdatabases
WHERE dbid = @.DBNum_to_Name
Print (@.ExecSQLcmd) -- For Debugging
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
INSERT INTO #tmp_RebuildIndexesPrameters (DatabaseName, TableName)
(SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
END
SELECT * FROM #tmp_RebuildIndexesPrameters -- For debugging
DROP TABLE #tmp_RebuildIndexesPrameters -- Delete the temporary
table
Thanks
-Matt_Try this:
USE master
GO
CREATE TABLE #TableNames (
DatabaseName sysname,
TableName sysname
)
DECLARE @.dbName sysname,
@.vcCommand varchar(4000)
DECLARE curDB CURSOR READ_ONLY FORWARD_ONLY
FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution',
'Northwind', 'pubs') -- skip these db's
OPEN curDB
FETCH NEXT
FROM curDB
INTO @.dbName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vcCommand = 'INSERT #TableNames SELECT ''' + @.dbName + ''', TABLE_NAME
FROM ' + @.dbName + '.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME'
EXECUTE (@.vcCommand)
FETCH NEXT
FROM curDB
INTO @.dbName
END
CLOSE curDB
DEALLOCATE curDB
SELECT *
FROM #TableNames
DROP TABLE #TableNames|||It works, but I am not too familiar with the cursor command. Would you
mind if you could explain it step by step? Also this needs to run on
SQL 2000 as well as 2005, and so far it looks like it does that.
Thanks
-Matt-|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1138393397.822646.317470@.z14g2000cwz.googlegroups.com...
> It works, but I am not too familiar with the cursor command. Would you
> mind if you could explain it step by step? Also this needs to run on
> SQL 2000 as well as 2005, and so far it looks like it does that.
> Thanks
> -Matt-
>
For help with cursors, go read through the books online.
In a nutshell, the code opens a cursor (recordset in programming parlance).
Grabs the first row
In a loop creates the SQL statement I gave you.
Executes the sql statement which loads info into the temp table
grabs the next row
Loops
Closes the cursor
selects data from the temp table.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I was just looking at it at BOL, but like most everything they
don't say it is layman's terms too well.
One additional question if you don't mind.
I need to add in null values to the other fields. DailyFragAmt,
DailyMaxRebuidAmt, WlyFragAmt, Priority, LastRun etc...
How would you recommend I do that? Process the query first and then
insert, or do it both at the same time.
collect all the table names.
Currently the loop goes around and around till it scans all the
databases but only pull from the current database you are in.
I am probably missing something that is obvious.
DECLARE @.ExecSQLcmd VARCHAR(2048) -- Creates Storage Space for SQL
Command
DECLARE @.DBNum_to_Name INT -- Creates Storage Space for
Database Name
CREATE TABLE #tmp_RebuildIndexesPrameters (
DatabaseName NVARCHAR(128),
TableName CHAR(255),
DailyFragAmt INT, -- Maximum daily fragmentation to allow (30
is default)
DailyMaxRebuidAmt INT, -- Maximum index size durring the w
to do a rebuild instead of defrag (100 Default)
W
lyFragAmt INT, -- Maximum wly fragmentation to allow (5is default)
W
lyMaxRebuidAmt INT, -- Maximum index size durring thew
end to do a rebuild instead of defrag (100 Default)LastRun DATETIME,
Priority INT, -- User Defined varable. (-1 Never Run; 1-999,
Priority with 1 being the highest.)
Completed INT, -- Required to prevent looping based upon
Priority and LastRun.
)
/*********************************
Loop though all DBs by DBID Number
Notes: The collection of Database
names and Table names should take
place durring this loop. Once the
data has been collected, it can be
compared to the existing paramerers
**********************************/
SELECT @.DBNum_to_Name = min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes '
FROM master.dbo.sysdatabases
WHERE dbid = @.DBNum_to_Name
Print (@.ExecSQLcmd) -- For Debugging
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
INSERT INTO #tmp_RebuildIndexesPrameters (DatabaseName, TableName)
(SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
END
SELECT * FROM #tmp_RebuildIndexesPrameters -- For debugging
DROP TABLE #tmp_RebuildIndexesPrameters -- Delete the temporary
table
Thanks
-Matt_Try this:
USE master
GO
CREATE TABLE #TableNames (
DatabaseName sysname,
TableName sysname
)
DECLARE @.dbName sysname,
@.vcCommand varchar(4000)
DECLARE curDB CURSOR READ_ONLY FORWARD_ONLY
FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution',
'Northwind', 'pubs') -- skip these db's
OPEN curDB
FETCH NEXT
FROM curDB
INTO @.dbName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vcCommand = 'INSERT #TableNames SELECT ''' + @.dbName + ''', TABLE_NAME
FROM ' + @.dbName + '.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME'
EXECUTE (@.vcCommand)
FETCH NEXT
FROM curDB
INTO @.dbName
END
CLOSE curDB
DEALLOCATE curDB
SELECT *
FROM #TableNames
DROP TABLE #TableNames|||It works, but I am not too familiar with the cursor command. Would you
mind if you could explain it step by step? Also this needs to run on
SQL 2000 as well as 2005, and so far it looks like it does that.
Thanks
-Matt-|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1138393397.822646.317470@.z14g2000cwz.googlegroups.com...
> It works, but I am not too familiar with the cursor command. Would you
> mind if you could explain it step by step? Also this needs to run on
> SQL 2000 as well as 2005, and so far it looks like it does that.
> Thanks
> -Matt-
>
For help with cursors, go read through the books online.
In a nutshell, the code opens a cursor (recordset in programming parlance).
Grabs the first row
In a loop creates the SQL statement I gave you.
Executes the sql statement which loads info into the temp table
grabs the next row
Loops
Closes the cursor
selects data from the temp table.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I was just looking at it at BOL, but like most everything they
don't say it is layman's terms too well.
One additional question if you don't mind.
I need to add in null values to the other fields. DailyFragAmt,
DailyMaxRebuidAmt, W
lyFragAmt, Priority, LastRun etc...How would you recommend I do that? Process the query first and then
insert, or do it both at the same time.
Monday, March 12, 2012
help with 2005 profiler
i'm trying to use the sql2005 profiler to run traces against both
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.
ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
> The trace templates are saved according to the SQL Server version. I'm not sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Sorry, David. I just noticed some files in that directory and incorrectly assumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trace templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\Templates\Microsoft SQL
Server\90
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created two custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the templates folder (I did not
> physically export them to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's not clear to me where the
> user templates are stored. I'm guessing they are in encrypted format either in the registration
> database or in another file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.
ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
> The trace templates are saved according to the SQL Server version. I'm not sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Sorry, David. I just noticed some files in that directory and incorrectly assumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trace templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\Templates\Microsoft SQL
Server\90
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created two custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the templates folder (I did not
> physically export them to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's not clear to me where the
> user templates are stored. I'm guessing they are in encrypted format either in the registration
> database or in another file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
help with 2005 profiler
i'm trying to use the sql2005 profiler to run traces against both
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Micro
soft
SQL Server\80
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
> The trace templates are saved according to the SQL Server version. I'm not
sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or
in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Micro
soft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Sorry, David. I just noticed some files in that directory and incorrectly as
sumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trac
e templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\
Templates\Microsoft SQL
Server\90
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created t
wo custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the tem
plates folder (I did not
> physically export them to disk). I think that the "Templates" folder store
s the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's n
ot clear to me where the
> user templates are stored. I'm guessing they are in encrypted format eithe
r in the registration
> database or in another file. Searching all files that contained the string
representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Micro
soft
SQL Server\80
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
> The trace templates are saved according to the SQL Server version. I'm not
sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or
in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Micro
soft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Sorry, David. I just noticed some files in that directory and incorrectly as
sumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trac
e templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\
Templates\Microsoft SQL
Server\90
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created t
wo custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the tem
plates folder (I did not
> physically export them to disk). I think that the "Templates" folder store
s the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's n
ot clear to me where the
> user templates are stored. I'm guessing they are in encrypted format eithe
r in the registration
> database or in another file. Searching all files that contained the string
representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
help with 2005 profiler
i'm trying to use the sql2005 profiler to run traces against both
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
>> i'm trying to use the sql2005 profiler to run traces against both
>> sql2000 and sql2005 databases. i created a trace template when
>> connected to a sql2000 db. i want to use the exact same template for
>> traces against sql2005. how do i get that template so that i can use
>> it when connected to a sql2005 instance? i've tried exporting,
>> importing, saving as a trace file, etc. i can't get that trace
>> template to load when connected to sql2005.
> The trace templates are saved according to the SQL Server version. I'm not sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Local machine, it seems:
>> C:\Program Files\Microsoft SQL
>> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Sorry, David. I just noticed some files in that directory and incorrectly assumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trace templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\Templates\Microsoft SQL
Server\90
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Local machine, it seems:
>> C:\Program Files\Microsoft SQL
>> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
> Tibor,
> I checked in that folder and did not see the (user) templates. I created two custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the templates folder (I did not
> physically export them to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's not clear to me where the
> user templates are stored. I'm guessing they are in encrypted format either in the registration
> database or in another file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
>> i'm trying to use the sql2005 profiler to run traces against both
>> sql2000 and sql2005 databases. i created a trace template when
>> connected to a sql2000 db. i want to use the exact same template for
>> traces against sql2005. how do i get that template so that i can use
>> it when connected to a sql2005 instance? i've tried exporting,
>> importing, saving as a trace file, etc. i can't get that trace
>> template to load when connected to sql2005.
> The trace templates are saved according to the SQL Server version. I'm not sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Local machine, it seems:
>> C:\Program Files\Microsoft SQL
>> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Sorry, David. I just noticed some files in that directory and incorrectly assumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trace templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\Templates\Microsoft SQL
Server\90
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Local machine, it seems:
>> C:\Program Files\Microsoft SQL
>> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
> Tibor,
> I checked in that folder and did not see the (user) templates. I created two custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the templates folder (I did not
> physically export them to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's not clear to me where the
> user templates are stored. I'm guessing they are in encrypted format either in the registration
> database or in another file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
Wednesday, March 7, 2012
Help Updating Data Across Servers
Hi,
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
Jothi
Hi,
If the database is residing in 2 servers and if you need to update data to
and fro then the only solution is using Linked servers and Triggers
But update Trigger using linked server will slow down the process.
Thanks
Hari
SQL Server MVP
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
|||hari,
Yes the data is acroos two servers.
How do i handle situations where a trigger fails.
DUe to non avialability of a server.
Thanks,
Jothi
"Hari Prasad" wrote:
> Hi,
> If the database is residing in 2 servers and if you need to update data to
> and fro then the only solution is using Linked servers and Triggers
> But update Trigger using linked server will slow down the process.
> Thanks
> Hari
> SQL Server MVP
> "Jothi" <Jothi@.discussions.microsoft.com> wrote in message
> news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
>
>
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
Jothi
Hi,
If the database is residing in 2 servers and if you need to update data to
and fro then the only solution is using Linked servers and Triggers
But update Trigger using linked server will slow down the process.
Thanks
Hari
SQL Server MVP
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
|||hari,
Yes the data is acroos two servers.
How do i handle situations where a trigger fails.
DUe to non avialability of a server.
Thanks,
Jothi
"Hari Prasad" wrote:
> Hi,
> If the database is residing in 2 servers and if you need to update data to
> and fro then the only solution is using Linked servers and Triggers
> But update Trigger using linked server will slow down the process.
> Thanks
> Hari
> SQL Server MVP
> "Jothi" <Jothi@.discussions.microsoft.com> wrote in message
> news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
>
>
Help Updating Data Across Servers
Hi,
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
JothiHi,
If the database is residing in 2 servers and if you need to update data to
and fro then the only solution is using Linked servers and Triggers
But update Trigger using linked server will slow down the process.
Thanks
Hari
SQL Server MVP
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>|||hari,
Yes the data is acroos two servers.
How do i handle situations where a trigger fails.
DUe to non avialability of a server.
Thanks,
Jothi
"Hari Prasad" wrote:
> Hi,
> If the database is residing in 2 servers and if you need to update data to
> and fro then the only solution is using Linked servers and Triggers
> But update Trigger using linked server will slow down the process.
> Thanks
> Hari
> SQL Server MVP
> "Jothi" <Jothi@.discussions.microsoft.com> wrote in message
> news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
>
>
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
JothiHi,
If the database is residing in 2 servers and if you need to update data to
and fro then the only solution is using Linked servers and Triggers
But update Trigger using linked server will slow down the process.
Thanks
Hari
SQL Server MVP
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>|||hari,
Yes the data is acroos two servers.
How do i handle situations where a trigger fails.
DUe to non avialability of a server.
Thanks,
Jothi
"Hari Prasad" wrote:
> Hi,
> If the database is residing in 2 servers and if you need to update data to
> and fro then the only solution is using Linked servers and Triggers
> But update Trigger using linked server will slow down the process.
> Thanks
> Hari
> SQL Server MVP
> "Jothi" <Jothi@.discussions.microsoft.com> wrote in message
> news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
>
>
Help Updating Data Across Servers
Hi,
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
JothiHi,
If the database is residing in 2 servers and if you need to update data to
and fro then the only solution is using Linked servers and Triggers:)
But update Trigger using linked server will slow down the process.
Thanks
Hari
SQL Server MVP
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>|||hari,
Yes the data is acroos two servers.
How do i handle situations where a trigger fails.
DUe to non avialability of a server.
Thanks,
Jothi
"Hari Prasad" wrote:
> Hi,
> If the database is residing in 2 servers and if you need to update data to
> and fro then the only solution is using Linked servers and Triggers:)
> But update Trigger using linked server will slow down the process.
> Thanks
> Hari
> SQL Server MVP
> "Jothi" <Jothi@.discussions.microsoft.com> wrote in message
> news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> > Hi,
> > We have two DataBases Residing on two different Servers. We would like to
> > set up real time uodates between these two servers.
> > We have an Inventory System on server A and a Tracking System on Server B.
> > Now an Transaction in Server B should Update a Certain Table or Tables on
> > Server A. and vice versa.
> >
> > I would like people to help me choose the Best soulution for this to work
> > successfully.
> >
> > I was leaning towards Linked Servers anfd The making use of triggers. Is
> > this a Good Option.
> >
> > But waht happens if the Trigger Fails how do we get the data Across to the
> > Servers.
> >
> > Thanks for your help in advance.
> >
> > Thanks,
> > Jothi
> >
>
>
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
JothiHi,
If the database is residing in 2 servers and if you need to update data to
and fro then the only solution is using Linked servers and Triggers:)
But update Trigger using linked server will slow down the process.
Thanks
Hari
SQL Server MVP
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>|||hari,
Yes the data is acroos two servers.
How do i handle situations where a trigger fails.
DUe to non avialability of a server.
Thanks,
Jothi
"Hari Prasad" wrote:
> Hi,
> If the database is residing in 2 servers and if you need to update data to
> and fro then the only solution is using Linked servers and Triggers:)
> But update Trigger using linked server will slow down the process.
> Thanks
> Hari
> SQL Server MVP
> "Jothi" <Jothi@.discussions.microsoft.com> wrote in message
> news:1B9DD1BE-0DFB-4480-BD24-97C4C10A97C2@.microsoft.com...
> > Hi,
> > We have two DataBases Residing on two different Servers. We would like to
> > set up real time uodates between these two servers.
> > We have an Inventory System on server A and a Tracking System on Server B.
> > Now an Transaction in Server B should Update a Certain Table or Tables on
> > Server A. and vice versa.
> >
> > I would like people to help me choose the Best soulution for this to work
> > successfully.
> >
> > I was leaning towards Linked Servers anfd The making use of triggers. Is
> > this a Good Option.
> >
> > But waht happens if the Trigger Fails how do we get the data Across to the
> > Servers.
> >
> > Thanks for your help in advance.
> >
> > Thanks,
> > Jothi
> >
>
>
Sunday, February 26, 2012
Help shrinking database doesnt work..
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:
backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log
GO
Please, Help Metry with this command on query analyzer
I hope this will solve ur problem
to get more explanation on this pl go through the help menu.
EXEC sp_dboption 'databasename', 'trunc. log on chkpt.', 'TRUE'
pl inform wheather it is working or not|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:
backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log
GO
Please, Help Me
I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..
if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..
wish you luck..|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:
backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log
GO
Please, Help Me|||check with ur tempdb size if it is comparativly larger in size right click select shrinkfile both (temp log and tempdata ) select compress pages and then truncate free space from the file.
have u observed with any temporary cursors tables etc u have created for manipulation and forgot to close it.
have u selected autoshrink option .
if possible observe with the table's if index is corupted or fragmented to check this u can use dbcc showcontig option and observe scan density%.
if it is not nearing 100% u can run dbcc indexfrag for de-fragmenting the table this is related with tr log file.
reply me the result.|||<quote>
by vishy
I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..
if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..
wish you luck..
</quote>
The problem is the data file i wil try out you're solution. Thnks.
I will reply soon.
backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log
GO
Please, Help Metry with this command on query analyzer
I hope this will solve ur problem
to get more explanation on this pl go through the help menu.
EXEC sp_dboption 'databasename', 'trunc. log on chkpt.', 'TRUE'
pl inform wheather it is working or not|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:
backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log
GO
Please, Help Me
I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..
if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..
wish you luck..|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:
backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log
GO
Please, Help Me|||check with ur tempdb size if it is comparativly larger in size right click select shrinkfile both (temp log and tempdata ) select compress pages and then truncate free space from the file.
have u observed with any temporary cursors tables etc u have created for manipulation and forgot to close it.
have u selected autoshrink option .
if possible observe with the table's if index is corupted or fragmented to check this u can use dbcc showcontig option and observe scan density%.
if it is not nearing 100% u can run dbcc indexfrag for de-fragmenting the table this is related with tr log file.
reply me the result.|||<quote>
by vishy
I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..
if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..
wish you luck..
</quote>
The problem is the data file i wil try out you're solution. Thnks.
I will reply soon.
Friday, February 24, 2012
Help setting up Peer-to-peer replication for approximately 500 db'
We are looking for a script that will setup Peer-to-peer replication for
approximately 500 databases.
Obviously the Wizard will do it, but it would be extremely time consuming.
And, after setting up the publication you would still have to setup the
Peer-to-Peer topology.
Is there a simple way script the entire process?
peer-to-peer is only really scalable to 10 or so nodes.
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
"Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
> We are looking for a script that will setup Peer-to-peer replication for
> approximately 500 databases.
> Obviously the Wizard will do it, but it would be extremely time consuming.
> And, after setting up the publication you would still have to setup the
> Peer-to-Peer topology.
> Is there a simple way script the entire process?
|||We have 2 SQL servers but need to do that 500 times. So the 10 nodes is not
an issue.
"Hilary Cotter" wrote:
> peer-to-peer is only really scalable to 10 or so nodes.
> --
> 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
> "Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
> news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
>
>
|||Once you've set it up for one node, scripting it out and amending the
scripts for each subsequent node shouldn't be too difficult:
http://www.replicationanswers.com/Script3.asp. You'll still need to get the
backup files restored on each node before commencing though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
approximately 500 databases.
Obviously the Wizard will do it, but it would be extremely time consuming.
And, after setting up the publication you would still have to setup the
Peer-to-Peer topology.
Is there a simple way script the entire process?
peer-to-peer is only really scalable to 10 or so nodes.
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
"Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
> We are looking for a script that will setup Peer-to-peer replication for
> approximately 500 databases.
> Obviously the Wizard will do it, but it would be extremely time consuming.
> And, after setting up the publication you would still have to setup the
> Peer-to-Peer topology.
> Is there a simple way script the entire process?
|||We have 2 SQL servers but need to do that 500 times. So the 10 nodes is not
an issue.
"Hilary Cotter" wrote:
> peer-to-peer is only really scalable to 10 or so nodes.
> --
> 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
> "Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
> news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
>
>
|||Once you've set it up for one node, scripting it out and amending the
scripts for each subsequent node shouldn't be too difficult:
http://www.replicationanswers.com/Script3.asp. You'll still need to get the
backup files restored on each node before commencing though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Subscribe to:
Posts (Atom)